CODEWITHSUNDEEP
sqloracle-database
Henkolicious
Henkolicious

Reputation: 1401

Oracle SQL update table row by row

I'm trying to update a DB-table, row by row, incrementing the ID for each row.

From a table like this

ID Foo Bar
------------
0  a   v
0  b   x
0  c   y
0  d   z
...

To

ID Foo Bar
------------
1  a   v
2  b   x
3  c   y
4  d   z
...

Is there a faster and better whay than this?

CREATE OR REPLACE PROCEDURE schema.update_foo_bar
AS
    v_i    int := 1;
BEGIN
  FOR rec IN (SELECT * FROM TEMP_FOO_BAR)
  LOOP   
        UPDATE TEMP_FOO_BAR fb
          SET fb.ID = v_i
            WHERE rec.Foo = fb.Foo
            AND rec.Bar = fb.Bar;

            v_i := v_i + 1;
  END LOOP;
END;

What I would like to do is simply assign the row-ID instead of checking the whole table on each iteration with the "where"-clause.

Upvotes: 0

Views: 959

Answers (2)

Ashutosh
Ashutosh

Reputation: 1

Why don't you delete that column and then run a alter table and add a column and a constraint to update the value every time.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520938

The ID values in your expected output can only be gotten by assuming some order in the records in your table. Assuming the Foo column determines this order, you can generate the output you expect using a simple query:

SELECT
    ROW_NUMBER() OVER (ORDER BY Foo) ID,
    Foo,
    Bar
FROM TEMP_FOO_BAR
ORDER BY ID;

The problem with using an update here is that as soon as you add more data to the table, you might be forced to do another update.

If you need ID to behave like an auto increment/identity column, then make it one, and let Oracle worry about maintaining the sequence as you add new data.

Upvotes: 2

Related Questions