SQL Server Why is my SQL update using a CASE statement creating null values?

eoigrqb6 于 2023-08-02 发布在其他

关注(0)|答案(2)|浏览(84)

I'm running the script below to update values in one column based on the value of another column in the same table. After running it, I did notice that the values for the columns in the CASE statement did update fine, BUT all of the other columns that should not have been included were and were changed to NULL... How did this happen?

UPDATE configsystem 
SET field1 = CASE
                 WHEN field2 =  32 THEN 0
                 WHEN field1 =  67 THEN 0
                 WHEN field1 =  99 THEN 1
                 WHEN field1 = 102 THEN 0
                 WHEN field1 = 105 THEN 5
             END
WHERE 1 = 1

See above - I ran the script expecting the values that needed updated were and then the rest of the fields that weren't in the CASE statement would not be updated in any way(but indeed they were and set to NULL)

Slight update from testing: I did remove the 'where' clause and that didn't cause a change in results(wasn't expecting it to). Not sure why an ELSE clause would be needed but, perhaps that's part of the cause?

sql-server

来源：https://stackoverflow.com/questions/76695111/why-is-my-sql-update-using-a-case-statement-creating-null-values

2条答案

按热度按时间

uklbhaso1#

Only UPDATE the desired rows.

UPDATE configsystem SET field1 =  
CASE
WHEN field2  = 32 THEN 0
WHEN field1  =  67 THEN 0
WHEN field1  =  99 THEN 1
WHEN field1  =  102 THEN 0
WHEN field1  =  105 THEN 5
END
WHERE field2  = 32 or field1 in (67, 99, 102, 105)

赞(0）回复(0）举报 2023-08-02

raogr8fs2#

Else field1 must be it, set it to itself, if you want to be sure.

赞(0）回复(0）举报 2023-08-02