Home » SQL & PL/SQL » SQL & PL/SQL » How to update a Column with specific Serial ? (DB & DEV 10g / Windows 7)
How to update a Column with specific Serial ? [message #418844] Tue, 18 August 2009 15:09 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

I have serial in the first table on primary key from 1 to 4 , and I want to repeat this serial in the foreign key in the second table .

that what I want to appear in the foreign key , I want the repeating take place till the end of the last record in the second table.

1
2
3
4
1
2
3
4
1
2
3
4
1
2
3
4
1
2

I tried with FOR .. LOOP but doesn't work with me Sad

DECLARE
   nn   NUMBER;
BEGIN
   SELECT COUNT (ID)
     INTO nn
     FROM persons;

   FOR i IN 1 .. nn
   LOOP
      UPDATE subject
         SET p_id = i
       WHERE ROWNUM = ROWNUM;
   END LOOP;
END;


What should I do ?!
Re: How to update a Column with specific Serial ? [message #418846 is a reply to message #418844] Tue, 18 August 2009 15:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: How to update a Column with specific Serial ? [message #418848 is a reply to message #418846] Tue, 18 August 2009 15:31 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If I understood that correctly, you are basically trying to do this with your code:

UPDATE subject
   SET p_id = ROWNUM;


Also a hint: ROWNUM is added when the rows are fetched so a "where ROWNUM = 2" will never return any rows, since ROWNUM never reaches 2.
Previous Topic: Select query while Insert/Update on a table
Next Topic: to know on which platform my database is running using select statement (merged 3)
Goto Forum:
  


Current Time: Wed Dec 07 18:35:42 CST 2016

Total time taken to generate the page: 0.14462 seconds