Update row in a table based on a column in the same table

user_5046677 0
Viewed 43

How can I update rows in a table based on values in a column in the same table?

TableA:

 col1 col2 col3 total col_num
 NULL NULL NULL 100   1
 NULL NULL NULL 200   2
 NULL NULL NULL 300   3

Result after update:

TableA:

col1  col2  col3 total col_num
100   NULL  NULL 100   1
NULL  200   NULL 200   2
NULL  NULL  300  300   3
sql-update tsql sql-server sql
2 Answers

Unless you resort to somehow dynamically constructing the SQL statement, you can't do this generically. However, for a closed set of columns, you could use a bunch of case expressions:

UPDATE tableA
SET col1 = CASE col_num WHEN 1 THEN total ELSE col1 END,
    col2 = CASE col_num WHEN 2 THEN total ELSE col2 END,
    col3 = CASE col_num WHEN 3 THEN total ELSE col3 END
user_24227760

You can use case expressions:

update tablea
set
    col1 = case when col_num = 1 then total end,
    col2 = case when col_num = 2 then total end,
    col3 = case when col_num = 3 then total end

You would possibly include some logic to update only non-null columns 

    update tablea
set
    col1 = case when col1 is null and col_num = 1 then total end,
    col2 = case when col2 is null and col_num = 2 then total end,
    col3 = case when col3 is null and col_num = 3 then total end
where
    (col1 is null and col_num = 1)
    or (col2 is null and col_num = 2)
    or (col3 is null and col_num = 3)
user_106767160
Related