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>

Tags:

2 Comments

  1. 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

    1. 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

Leave a Reply

Your email address will not be published. Required fields are marked *