4

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

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
typhoid
  • 171
  • 1
  • 2
  • 4
  • I should have mentioned that var1 is set above by finding the max value for table.column within this table. – typhoid Nov 08 '11 at 15:45
  • There's a similar question [here](http://stackoverflow.com/questions/4749669/variable-manipulation-in-oracle/4751469#4751469). – Allan Nov 08 '11 at 16:57

3 Answers3

2

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.

APC
  • 144,005
  • 19
  • 170
  • 281
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.

Allan
  • 17,141
  • 4
  • 52
  • 69
1

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.

Lost in Alabama
  • 1,653
  • 10
  • 16
  • Sorry I cant do that because the current values in table.column are not unique. this approach would just increment their values by one step. What I really need is a way to make these particular columns (where column2 = "value") unique across all column values for every record. – typhoid Nov 08 '11 at 15:44
  • 1
    @typhoid: I'm not sure what your specific needs are in this case, but have you considered using a sequence? A sequence guarantees you unique values without the overhead of finding the max value. If you also need to make the combination `table.column` and `table.column2` unique then you should also consider adding a unique constraint to this pair of columns. – WoMo Nov 08 '11 at 16:30