Updating rows conditionally with a gapless ascending value

gaps-and-islandsoracleoracle-11g-r2

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.

Best Answer

See this discussion on AskTom about the way expressions are evaluated. The decode function does perform short-circuit in most cases: right-side expressions are not evaluated if a condition on the left is evaluated to true. Sequence are special however, they are evaluated for all lines in all cases.

The easiest workaround is to use a function that calls the sequence:

SQL> CREATE SEQUENCE seq;

Sequence created

SQL> CREATE OR REPLACE FUNCTION f RETURN NUMBER
  2  AS
  3     l_result NUMBER;
  4  BEGIN
  5     SELECT seq.nextval INTO l_result FROM dual;
  6     RETURN l_result;
  7  END;
  8  /

Function created

SQL> UPDATE t1 SET letter='x',
  2     number2 = DECODE(letter,'a',f,'d', f,'e', f,NULL);

7 rows updated

SQL> select * from t1;

LETTER    NUMBER1    NUMBER2
------ ---------- ----------
x               1          1
x               2 
x               3 
x               4          2
x               5          3
x               6 
x               7 

7 rows selected

As you can see the function is evaluated only when the DECODE expression requires it.

You can work a pure SQL solution in most cases. If we assume that the table has a PK (I'll take number1), this self-join SQL solution would work:

SQL> UPDATE t1 t1_out
  2     SET letter  = 'x',
  3         number2 = CASE WHEN letter IN ('a', 'd', 'e') --
  4                        THEN (SELECT COUNT(*)
  5                                FROM t1 t1_in
  6                               WHERE letter IN ('a', 'd', 'e')
  7                                 AND t1_in.number1 <= t1_out.number1)
  8                   END;

7 rows updated

SQL> select * from t1;

LETTER    NUMBER1    NUMBER2
------ ---------- ----------
x               1          1
x               2 
x               3 
x               4          2
x               5          3
x               6 
x               7 

7 rows selected

You have probably tried an analytics solution. It is possible to write a query that will return the appropriate result set but update and analytics don't work very well together (analytics tend to produce non-updatable views).

Related Question