CODEWITHSUNDEEP
t-sqlsybase
KentZhou
KentZhou

Reputation: 25573

how to write one sql to update data?

Suppose I have data in table like:

id    level  flag
1     1      0  
1     2      0  
1     3      1  
1     4      0  
1     5      1  
1     6      0  
1     7      0  
1     8      1  
1     9      1  
1     10     0     

2     1      0  
2     2      0  
2     3      0  
2     4      0  
2     5      1  
2     6      1  
2     7      1

......

I want to update flag to 0 after first 1 value for flag. For example, with above sample data, for id = 1, the first flag value =1 is level=3, then all flag values for level>3 should be updated to 0.

For id = 2, should update flag = 0 for all level>5

How to implement it with sql even one sql statement?

Upvotes: 0

Views: 58

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can do this with an exists statement:

update table t
    set flag = 0
    where exists (select 1
                  from table t2
                  where t2.id = t.id and
                        t2.level < t.level and
                        t2.flag = 1
                 );

Upvotes: 0

lc.
lc.

Reputation: 116498

You should be able to do this with a WHERE EXISTS on the same table:

UPDATE t1
SET flag = 0
FROM TheTable t1
WHERE EXISTS (
    SELECT 1
    FROM TheTable t2
    WHERE t2.id = t1.id
    AND t2.level < t1.level
    AND t2.flag = 1
)

SQL Fiddle demo

Upvotes: 1

Related Questions