CODEWITHSUNDEEP
sql-serversetconditional-statements
likertuban
likertuban

Reputation: 1

MSSQL update and set a column with certain value

I have a problem on updating a column in MSSQL, so basically i have a database like this 

no     U1     U2     U3     U4
1      12     -1     10     -1
2      12     15     -1     11
3      -1     17     10     11
4      12     -1     10     11

My goal is to update columns that have -1 value look on no. 1, it have two -1 value on U2 and U4, what I want is to update the first -1 value (which is U2 from no 1 on that example), with a value I want/I declare, and I just want to update from no 1, not updating no 4 U2 too...

How to accomplish this?

Upvotes: 0

Views: 139

Answers (1)

Jan Zahradník
Jan Zahradník

Reputation: 2497

Use where clause of UPDATE statement eg:

update table1
set U1 = case U1 when -1 then 123 else U1 end,
    U2 = case when U1 != -1 and U2 = -1 then 123 else U2 end,
    U3 = case when -1 not in (U1, U2) and U3 = -1 then 123 else U3 end,
    U4 = case when -1 not in (U1, U2, U3) and U4 = -1 then 123 else U4 end
where [no] = (select top 1 [no] from table1 where -1 in (U1, U2, U3, U4))

Upvotes: 2

Related Questions