Updating rows conditionally with a gapless ascending value
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&gt; CREATE SEQUENCE seq;

Sequence created

SQL&gt; 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&gt; UPDATE t1 SET letter='x',
  2     number2 = DECODE(letter,'a',f,'d', f,'e', f,NULL);

7 rows updated

SQL&gt; 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&gt; 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 &lt;= t1_out.number1)
  8                   END;

7 rows updated

SQL&gt; 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).