Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

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).

share|improve this answer
    
+1 Excellent solutions. I'll post mine, but depending on the situation either of yours is probably better. –  Leigh Riffel Mar 8 '11 at 14:09
add comment

This isn't an Update statement, but it does meet the other requirements of the question.

MERGE INTO t1 b
USING (SELECT letter, number1, number2, 
   Row_Number() OVER (PARTITION BY 
      DECODE(letter,'a',1,'d',1,'e',1,0) ORDER BY number1) SectionRow FROM t1) a 
ON (a.number1 = b.number1)
WHEN MATCHED THEN UPDATE SET b.letter='x', 
   b.number2 = 
      DECODE(a.letter,'a',SectionRow,'d',SectionRow,'e',SectionRow,NULL);
share|improve this answer
    
+1: Nice use of the MERGE statement –  Vincent Malgrat Mar 8 '11 at 14:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.