A user has a table they would like to update. Depending on the data in other columns they want a particular column to be updated with an ascending value starting from 1 that is gap-less. The rows that will not contain this ascending value also need to be updated. Is there a way to do this with a single UPDATE statement?
Create sample data:
CREATE TABLE t1 AS ( SELECT CHR(ASCII('a')+rownum-1) letter, rownum Number1, 0 Number2 FROM dual connect by rownum<=7);
Update statement that assigns an ascending rownum, but it isn't gap-less:
UPDATE t1 SET Letter=’x’, Number2 = DECODE(letter,’a’,rownum,’d’,rownum,’e’,rownum,NULL);
Using a sequence returns the same results:
CREATE SEQUENCE s1; UPDATE t1 SET letter=’x’, number2 = DECODE(letter,’a’,s1.nextval,’d’, s1.nextval,’e’, s1.nextval,NULL);
The data should look like this when complete:
L NUMBER1 NUMBER2 - ---------- ---------- x 1 1 x 2 x 3 x 4 2 x 5 3 x 6 x 7
I'm not looking for a trick applicable only to my specific test case, but a general solution to the problem.