Home » SQL & PL/SQL » SQL & PL/SQL » increment the values of a column which contains PK (9.0.4.0)
increment the values of a column which contains PK [message #319752] Mon, 12 May 2008 23:25 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,
I have created one table.It has ID column.I have created Primary Key constraint on ID column.
I have values of ID column as follows.
ID
---
0
1
2
3
..
..
100

Now I want to Increase the values of the column as
ID
---
1      Eg :(  0 +1)
2          (  1 +1) 
3          (  2 +1)
4          (  3 +1) 
..      ....
..      ... 
I want to save this values Permanently.
Please help me to write a query for this.

Thanks in advance.
Re: increment the values of a column which contains PK [message #319754 is a reply to message #319752] Mon, 12 May 2008 23:28 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
After Messages: 216, you still choose to ignore posting guidelines as stated in URL below.
http://www.orafaq.com/forum/t/88153/0/

You're On Your Own (YOYO)!
Re: increment the values of a column which contains PK [message #319768 is a reply to message #319752] Tue, 13 May 2008 00:18 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
So what you are saying is that you want to update the table and set the value of the id column to be the value of the id column plus 1 and that you are commited to making these changes permanent.
<sarcasm on>
Hmmmm, that's a toughie... :/
Let me read up on some of the basics of DML then I'll send you a script to do your homework for you<sarcasm off>
Re: increment the values of a column which contains PK [message #319772 is a reply to message #319768] Tue, 13 May 2008 00:34 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Of course, a simple UPDATE would be enough.

However, if there are foreign keys that reference this particular primary key, user71408 might find himself in a trouble.
Re: increment the values of a column which contains PK [message #319775 is a reply to message #319772] Tue, 13 May 2008 00:39 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Of course, a simple UPDATE would be enough.


unfortunately not.
SQL> update employees set employee_id = employee_id +1;
update employees set employee_id = employee_id +1
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.DEPT_MGR_FK) violated - child record found

OP will have to look into either deferring or disabling the PK constraint first.

Quote:
user71408 might find himself in a trouble.
I think that user71408's inability to do any research for him/her self is going to cause him/her much more trouble.

[Updated on: Tue, 13 May 2008 00:40]

Report message to a moderator

Re: increment the values of a column which contains PK [message #319792 is a reply to message #319775] Tue, 13 May 2008 01:21 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I wrote the Pl/SQL block as follows..
Begin
For i in Reverse 1..n Loop
Update Emp set Col1=i+1 where Col1=i;
End loop;
Commit;
End;


Re: increment the values of a column which contains PK [message #319793 is a reply to message #319768] Tue, 13 May 2008 01:23 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,
I have tried in the following way also.

1.Disable the PK constraint on the column.
2. Update Emp set Col1=Col1+1;
3. Then Enable the constraint...

Re: increment the values of a column which contains PK [message #319821 is a reply to message #319793] Tue, 13 May 2008 02:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
.. and?
Re: increment the values of a column which contains PK [message #319874 is a reply to message #319821] Tue, 13 May 2008 05:35 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I got Foreign key constraint violation .So I am getting problem to update the column...with out updating descendents I want to modify as per the requirement...
Re: increment the values of a column which contains PK [message #319875 is a reply to message #319874] Tue, 13 May 2008 05:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could modify the child constraints to be Deferrable.
That way the constraint only gets evaluated when the transaction ends Then you update the PK column with a single update, update all of the FK columns with a single update each, and commit once at the end of the process.
icon3.gif  Re: increment the values of a column which contains PK [message #319891 is a reply to message #319752] Tue, 13 May 2008 06:18 Go to previous messageGo to next message
srinu_erp
Messages: 3
Registered: January 2008
Junior Member
Hi,
the better way is to use a sequence.


regards,
srinu

[Updated on: Tue, 13 May 2008 06:19]

Report message to a moderator

Re: increment the values of a column which contains PK [message #319894 is a reply to message #319891] Tue, 13 May 2008 06:22 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Quote:
the better way is to use a sequence.


And how would a sequence overcome the issue of the constraint violation for this particulat task?
Re: increment the values of a column which contains PK [message #319900 is a reply to message #319894] Tue, 13 May 2008 06:32 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Once this job is done, could we know what is behind it? What was the requirement so that you (user71408) have to modify primary key column values? I mean, it is just an ID, a number. What difference does it make if its value is 44552 and not 44551? Who cares? Why?
Re: increment the values of a column which contains PK [message #319906 is a reply to message #319900] Tue, 13 May 2008 06:43 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
According to a thread in another forum (OTN) user71408 is, in fact, a teacher and one of his pupils asked this question. :/

[Updated on: Tue, 13 May 2008 06:43]

Report message to a moderator

Re: increment the values of a column which contains PK [message #319914 is a reply to message #319906] Tue, 13 May 2008 06:58 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh.
Re: increment the values of a column which contains PK [message #319926 is a reply to message #319906] Tue, 13 May 2008 07:18 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
pablolee wrote on Tue, 13 May 2008 13:43
According to a thread in another forum (OTN) user71408 is, in fact, a teacher and one of his pupils asked this question. :/

omg..
Previous Topic: File Integrity check
Next Topic: Derby database
Goto Forum:
  


Current Time: Thu Dec 08 23:55:03 CST 2016

Total time taken to generate the page: 0.10240 seconds