MySQL query CASE WHEN THEN in UPDATE statement-CodePudding

I am trying to execute the following statement but it is resulting in error. There is not much to explain the situation here as the query itself is self explanatory. However, I will attach the error below:

Query

UPDATE swipes SET
CASE
    WHEN swp_from = :me AND swp_to = :user THEN first_swp = 'rewind'
    WHEN swp_to = :me AND swp_from = :user THEN second_swp = 'rewind'
END

Error

Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASE WHEN swp_from = '39' AND swp_to = '22' THEN f...' at line 2 in G:\xampp\htdocs\kelvie\processes\revoke.php on line 19

I know that the keywords SET and CASE cannot come together which is causing this error. But this is how the query needs to be framed. With this as an example can somebody guide me the proper way to frame the query to match the exact same expectation?

CodePudding user response：

In UPDATE syntax, you must have SET column = <expression>. You can set multiple columns, but they must all be column = <expression>. You can't wrap the assignments in a CASE statement.

Here's how to write your UPDATE:

UPDATE swipes SET
  first_swp  = CASE WHEN swp_from = :me AND swp_to = :user THEN 'rewind'
                    ELSE first_swp END,
  second_swp = CASE WHEN swp_to = :me AND swp_from = :user THEN 'rewind' 
                    ELSE second_swp END;

The ELSE means if the condition is not true, then set first_swp = first_swp, i.e. no change.

You could also do this in two updates, which makes the code more clear and if the indexes support it, should run a lot more quickly because it won't have to examine every row.

START TRANSACTION;

UPDATE swipes SET first_swp = 'rewind'
WHERE swp_from = :me AND swp_to = :user;

UPDATE swipes SET second_swp = 'rewind'
WHERE swp_to = :me AND swp_from = :user;

COMMIT;

The transaction is to make sure both changes are committed or else neither.

CodePudding user response：

You cannot choose which column you want to update dynamically. The case has to be after the =

UPDATE swipes 
SET first_swp = CASE WHEN swp_from = :me AND swp_to = :user 
                     THEN 'rewind' 
                     ELSE first_swp 
                END,
   second_swp = CASE WHEN swp_to = :me AND swp_from = :user 
                     THEN 'rewind' 
                     ELSE second_swp 
                END