SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    [Oracle] Filling the values of a new column

    Anyone know how I can loop through a table, inserting an incrementing value into a newly added column so I can set this column to be not null?

    Thanks!
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  2. #2
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is sometime since I have used Oracle, but could rownum come to the rescue here?

    update table set newcol = rownum

    Do the values in the new column all have to be different? Could you set them all to zero (or some other value)?

    Mike

  3. #3
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The column I'm filling will be a new primary key column so yes, they all must be unique.
    Someone else on another forum suggested the following proc, but if anyone else has a simpilar way then I'm open to suggestion.

    Code:
    DECLARE
    	rowCount NUMBER(8):=0;
    	myRow ROWID:=NULL;
    
    	CURSOR mycursor IS
    	SELECT ROWID 
    	FROM mytable
    	WHERE my_id is NULL
    	AND   ROWNUM = 1;
    BEGIN
    	LOOP
    		OPEN mycursor;
    		FETCH mycursor INTO myRow;
    		EXIT WHEN mycursor%NOTFOUND;
    		CLOSE mycursor;
    		UPDATE mytable
    		SET jn_db_user_id = rowCount 
    		WHERE ROWID = myRow;
    		rowCount:=rowCount+1;
    	END LOOP;
    	CLOSE mycursor;
    	COMMIT;
    END;
    /
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •