PL/SQL Update that increments a variable

Question

I found the following approach on http://haacked.com/archive/2004/02/28/sql-auto-increment.aspx but have had no luck with PL/SQL. Any thoughts?

update table    
set table.column = var1 = var1 + 1    
where table.column2 = "value';

It appears not to like the incrementing of var1 in the second half od the set line

Solution 1

A bit of PL/SQL to hold the original max ID. Using ROWNUM solves the problem of incrementing a count:

declare
    n pls_integer;
begin
    select max(col1)
    into n
    from your table;

    update your_table
    set col1 = n + rownum
    where col2 = 'value';
end;

This will give you a unique ID for COL1 which won't collide with COL1 for other value of COL2.

Solution 2

Based on what's going on in the link you provided, I think what you really want is this:

update your_table    
set your_table.column = rownum
where your_table.column2 = 'value';

This will set each row in the table for 'value' to a unique integer, starting at 1. The values will only be unique for that value however. You could add rownum to the current max value to set them all greater than any existing values (as seen in @APC's answer).

It should be noted that this is not safe to do if there's any chance of multiple inserts and/or updates on this column happening simultaneously (i.e. it's probably okay to do this as a one-time fix, but it should not be a regular routine (and certainly shouldn't be used in code)). In that case you should definitely be using a sequence.

Solution 3

This might work better for you:

update table
set table.column = table.column + 1
where table.column2 = "value';

Sounds like what you need is what @Wolf said, a sequence. Otherwise you could do this:

update table
set table.column = 
  (select max(table.column) + 1
   from table)
where table.column2 = "value';

Hopefully the column2 values are unique, or else you will get duplicates.