Always include a WHERE clause in conditional UPDATE statements

When executing an UPDATE or DELETE statement that conditionally updates a subset of rows in SQL table, you must include a WHERE clause to scope the data set you're mutating. Otherwise, your query is not scoped and you will most likely be committing unwanted updates. Example:

UPDATE table_name
SET column_a = (
  CASE column_b
    WHEN 'value1' THEN 'some value'
    ...
    WHEN 'valuen' THEN 'another value'
  END
)

This statement appears to be updating only a subset of the table. If executed as is, column_a will be set to NULL for rows where column_b does not match any conditions in the CASE statement and updated as expected where matching. To ensure that you are updating the correct rows, append the following to it:

WHERE column_b IN ('value1', ..., 'valuen');

If you have a fallback or default value for column_b, this issue could be circumvented by including an ELSE in your CASE statement.

• Basic SQL UPDATE
• Flags to prevent this in MySQL