| increment the values of a column which contains PK [message #319752] |
Mon, 12 May 2008 23:25  |
user71408 Messages: 300 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.
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 #319768 is a reply to message #319752 ] |
Tue, 13 May 2008 00:18   |
pablolee Messages: 1038 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   |
Littlefoot Messages: 6174 Registered: June 2005 Location: Croatia, Europe |
Senior Member |
|
|
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   |
pablolee Messages: 1038 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]
|
|
|
|
|
| Re: increment the values of a column which contains PK [message #319821 is a reply to message #319793 ] |
Tue, 13 May 2008 02:25   |
Frank Messages: 5761 Registered: April 2002 Location: NL |
Senior Member |
|
|
|
.. and?
|
|
|
|
| Re: increment the values of a column which contains PK [message #319875 is a reply to message #319874 ] |
Tue, 13 May 2008 05:42   |
JRowbottom Messages: 3069 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.
|
|
|
Re: increment the values of a column which contains PK [message #319891 is a reply to message #319752 ] |
Tue, 13 May 2008 06:18   |
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]
|
|
|
| Re: increment the values of a column which contains PK [message #319894 is a reply to message #319891 ] |
Tue, 13 May 2008 06:22   |
pablolee Messages: 1038 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   |
Littlefoot Messages: 6174 Registered: June 2005 Location: Croatia, Europe |
Senior Member |
|
|
|
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   |
pablolee Messages: 1038 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]
|
|
|
| Re: increment the values of a column which contains PK [message #319914 is a reply to message #319906 ] |
Tue, 13 May 2008 06:58   |
Littlefoot Messages: 6174 Registered: June 2005 Location: Croatia, Europe |
Senior Member |
|
|
|
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  |
Frank Messages: 5761 Registered: April 2002 Location: NL |
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..
|
|
|