SQL - WHERE clause on each SET command in UPDATE?

问题:

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

回答1:

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;

回答2:

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.

回答3:

Nope. Make it two updates:

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

On second thought, you could use sub-queries or the case statement...

UPDATE table SET val= ( case when someproperty = 1 then X when someproperty = 2 then Y else val END )

You may need to make that a sub query like this:

UPDATE table t1 SET val = ( select CASE when someproperty = 1 then X when someproperty = 2 then Y ELSE val END from table t2 where t1.primarykey = t2.primary key )

回答4:

UPDATE TABLE
SET VAL CASE SOMEPROPERTY WHEN 1 THEN X WHEN 2 THEN Y END

回答5:

A compact and easily scaleable way:

UPDATE table1 SET val=ELT(FIND_IN_SET(someproperty, '1, 2'), X, Y);

make the query this way:

$condition = array(1, 2);
$newvals = array('X', 'Y');
$query = "UPDATE table1 SET val=ELT(FIND_IN_SET(someproperty, '". implode(',', $condition). "', ". implode(', ', $newvals). ")";

Use prepare_query to avoid SQL syntax errors if you deal with string values.