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:xampphtdocskelvieprocessesrevoke.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?
2
Answers
You cannot choose which column you want to update dynamically. The
case
has to be after the=
In UPDATE syntax, you must have
SET column = <expression>
. You can set multiple columns, but they must all becolumn = <expression>
. You can’t wrap the assignments in a CASE statement.Here’s how to write your UPDATE:
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.
The transaction is to make sure both changes are committed or else neither.