SQL - WHERE clause on each SET command in UPDATE?

Question

I'm trying to create an SQL query in PHP to update a table. Is it possible to have a different WHERE clause for each affected row?

eg something like:

UPDATE table 
SET val=X WHERE someproperty = 1,
SET val=Y WHERE someproperty = 2

etc?

Any help appreciated. Thanks

Answer 1

Yes, you can with a CASE statement.

UPDATE table
SET val = CASE someproperty  
           WHEN 1 THEN x 
           WHEN 2 THEN y
           ....
          ELSE
           val
          END

Now, there is concern that one CASE statement is less readable when compared to several UPDATE statements. There is a valid argument here. For example, when 1000 rows are being updated, it just feels and looks better to use several UPDATE statements rather than 1000 different conditions to a single CASE.

However, sometimes a CASE statement is more appropriate. If, for example, you are updating rows based on some trait, say the even or odd nature of a field's value the table, then a CASE statement is a wonderfully concise and maintainable way to update rows in the table without having to resort to a huge number of UPDATE statements that all share a specific type of logic. Take this for example:

UPDATE table
SET val = CASE MOD(someproperty, 2)  
           WHEN 0 THEN x 
           WHEN 1 THEN y
          END

This expression takes the modulus of someproperty and, when 0 (even), assigns value x to val and, when 1 (odd), assigns value y to val. The greater the volume of data being updated by this statement, the cleaner it is compared to doing so by multiple UPDATE statements.

In short, CASE statements are sometimes just as readable/maintainable as UPDATE statements. It all depends on what you are trying to do with them.

EDIT: Added the ELSE clause to be extra safe. The OP may be interested in updating only specific rows so the rest should remain as they prior to the UPDATE.

EDIT: Added a scenario where the CASE statement is a more effective approach than multiple UPDATE statements.

Answer 2

You cannot have multiple WHERE clauses for any SQL statement, however you can use a CASE statement to accomplish what you are trying to do. Another option that you have is to execute multiple UPDATE statements.

Here is a sample using the CASE statement:

UPDATE table
SET val = (
    CASE someproperty
        WHEN 1 THEN X
        WHEN 2 THEN Y
        ELSE val
    END
);

Here is a sample using multiple UPDATE statements:

UPDATE table SET val=X WHERE someproperty = 1;
UPDATE table SET val=Y WHERE someproperty = 2;