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)

  • 2
    If you specify a column should be updated then it will always be updated(Either with new value according to the condition or with original value). – JSapkota Mar 18 '16 at 4:39

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 what x_specific_condition or y_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:

UPDATE some_table
SET column_x = new_value_for_x
WHERE some_more_conditions
  AND x_specific_condition;

UPDATE some_table
SET column_y = new_value_for_y
WHERE some_more_conditions
  AND y_specific_condition;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.