CODEWITHSUNDEEP
sqlsybase
Shobha M
Shobha M

Reputation: 33

Update statement to increment column value

Please consider following setup

Control Group   Sequence
-------------------------
Cont1   Group1  0
Cont2   Group1  1
Cont3   Group1  2
Cont3   Group2  0
Cont2   Group2  1
Cont1   Group2  2

Increment sequence with +1 for sequence >= sequence of cont2 and for respective Group

The result should be

Control Group   Sequence
------------------------
Cont1   Group1  0
Cont2   Group1  2
Cont3   Group1  3
Cont3   Group2  0
Cont2   Group2  2
Cont1   Group2  3

Can any one help me to construct a SQL query to get above result?

Upvotes: 1

Views: 214

Answers (2)

hmr2020
hmr2020

Reputation: 1

You can also try an alternative solution using OUTER APPLY:

UPDATE s
    SET s.[Sequence] = s.[Sequence]+1
FROM setup s
OUTER APPLY
(
    SELECT s2.[Sequence]
    FROM setup s2
    WHERE s2.[Group] = s.[Group] AND s2.[Control] = 'Cont2'
)_
WHERE s.[Sequence] >= _.[Sequence]

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270351

I think this does what you want:

update setup
    set sequence = sequence + 1
    where sequence >= (select s2.sequence
                       from setup s2
                       where s2.group = s.group and s2.control = 'Cont2'
                      );

Upvotes: 1

Related Questions