Update query with CASE

[ginerjm]

Having a bit of a problem forming an update query using a CASE statement in it. Can successfully do when using a SELECT query but my syntax is wrong with all the attempts I've made to use UPDATE. Here is my working select query

 

$q = "select machine_seq,
CASE machine_seq
WHEN $prev_seq then $seq
WHEN $seq then $prev_seq
END AS new_machine_seq
from workout_routines
where user_id = '$userid' and routine_id = '$routine' and
machine_seq in ($prev_seq,$seq)";

 

Can someone alter this to an UPDATE query for me? I'm trying to swap two values of the column named 'machine-seq'.

[Ch0cu3r]

You assign the column value as the case

UPDATE workout_routines
SET machine_seq =
	CASE machine_seq
	WHEN $prev_seq THEN $seq
	WHEN $seq THEN $prev_seq
	END
WHERE user_id = '$userid' AND routine_id = '$routine' AND
machine_seq IN ($prev_seq,$seq)

[ginerjm]

Thx muchly!