The example below demonstrates the use of the MySQL CASE statement to cleverly construct a bulk update statement. A huge performance boost over performing individual update statements, especially if you have a large number of them to execute.
SET position =
<cfloop from="1" to="#length#" index="i">
WHEN element_id = #Val(ListGetAt(arguments.positions, i), 2)# THEN #i#
Thanks for this helpful post.
Could you help my by posting the equivalent of this for a single update? I'm having trouble getting my head around CASE WHEN statements. What would be your take on the equivalent clause for a single row update with WHERE clause?
If you are only updating a single row, the CASE statement wouldn’t even be necessary. If you have a primary key on your table, you can simply check it against the row you wish to update, i.e.
SET myfield = ‘value‘
WHERE myprimarykey = rowid