Is it possible to write the following query in a simpler way, maybe without subquery?
UPDATE @table SET s_qty = 0 WHERE id IN ( SELECT id FROM ( SELECT CASE WHEN ROW_NUMBER() OVER (PARTITION BY s_sid ORDER BY CASE WHEN i_qty=0 THEN 2 ELSE 1 END,i_sid)>1 THEN 'clear' ELSE '' END AS do, id FROM @table ) t WHERE t.do = 'clear' );
UPDATE D SET s_qty = 0 FROM ( SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY s_sid ORDER BY CASE WHEN i_qty = 0 THEN 2 ELSE 1 END, i_sid) FROM @table ) D WHERE D.rn > 1
Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You names are too short, and useless to other people.
Right now all we can see is a generic table of nothing in particular. then there is an ID column that may or may not be an identifier or a key but it is of "nothing in particular". A CASE expression in an ORDER BY clause, the way you are using it, is generally done with and exist () predicate.
We have no idea what an "S" attribute is as in "s_qty", what kind of attribute property an "_sid" is, etc.
Sample data will also help.
I have the feeling, because of the case expressions, that you are trying to mimic procedural if–then–else control flow in SQL.