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.
<cfquery>
UPDATE mytable
SET position =
CASE
<cfloop from="1" to="#length#" index="i">
WHEN element_id = #Val(ListGetAt(arguments.positions, i), 2)# THEN #i#
</cfloop>
END
</cfquery>
Hi,
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?
Woul
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.
UPDATE mytable
SET myfield = ‘value‘
WHERE myprimarykey = rowid