I have a query that looks like below:
UPDATE some_table
SET column_x = CASE WHEN x_specific_condition THEN new_value_for_x ELSE column_x END,
column_y = CASE WHEN y_specific_condition THEN new_value_for_y ELSE column_y END,
WHERE some_more_conditions
Problem with above is, each column (x, y) still gets updated with their own value if some_more_conditions return true irrespective of their specific conditions returning true. I tried removing ELSE from above, but no luck
I am combining as some_more_conditions are same for both cases, I think its better to perform all in 1 update (suggestions welcome)
Do you know if there is a way that can I can perform the above update in 1 query by skipping the individual columns where the specific conditions do not match (basically avoid overwriting of same values)
Best Answer
Both columns specified in the SET clause of your UPDATE are going to be touched (written to) for all the rows matching the
some_more_conditions
, no matter whatx_specific_condition
ory_specific_condition
may evaluate to. There is no syntax to work around that while sticking to a single statement.So, either live with that or go for two statements: