Update the order of multiple records in one query

20 June 2011

I bet you have had this problem once before: a bunch of pages/documents/whatever with an order column in the DB that all needed to be updated when you changed the order with this nifty jQuery UI Sortable drag-n-drop script.

The jQuery sortable function passes back a nice Array with the id's, and the function that updates the database looks easy. A simple loop with an update query and off you go. But what if you need to update 100 records. This would result in 100 queries being executed back to back. This is a massive load for the server and you don't want that.

Best to do is create one query that updates all records. The query format for this is as follows:

UPDATE documents
    SET doc_order = CASE doc_id
        WHEN $id1 THEN '1'
        WHEN $id2 THEN '2'
        WHEN $id3 THEN '3'
    END
WHERE id IN ($id1,$id2,$id3)


Translated to a PHP loop, it could look like this:
$query = "UPDATE documents SET doc_order = CASE doc_id";
$i=0;
foreach ($idArray AS $id) {
    $sql .= "WHEN '$id' THEN '$i'";
    $i++;
}
$sql .= "END WHERE id IN (";
foreach ($idArray AS $id) {
    $sql .= $id.', ';
}
$sql = trim($sql, ', ');
$sql .= ')';


Or in Coldfusion:

<cfquery name="q" datasource="#this.dsn[1]#">
    UPDATE docs
      SET doc_order = CASE doc_id
        <cfloop index="x" from="1" to="#arrayLen(doc_array)#">
          <cfoutput>WHEN '#doc_array[x]#' THEN '#x#'</cfoutput>
        </cfloop>
      END
    WHERE doc_id IN (
      <cfloop index="x" from="1" to="#arrayLen(doc_array)#">
        <cfoutput>'#doc_array[x]#'</cfoutput>
        <cfif x < arrayLen(doc_array)>, </cfif>
      </cfloop>
    )
</cfquery>


I hope this will help you in speeding up your application!

Update 21 june 2011:
Thanks to my CF-guru Tjarko, I was able to create an even shorter CF query:

<cfquery name="q" datasource="#this.dsn[1]#">
    UPDATE docs
      SET doc_order = CASE doc_id
        <cfloop index="x" from="1" to="#arrayLen(doc_array)#">
          <cfoutput>WHEN '#doc_array[x]#' THEN '#x#'</cfoutput>
        </cfloop>
      END
    WHERE doc_id IN (#listQualify(arrayToList(doc_array),"'")#)
</cfquery>
You must have JavaScript enabled to use this form!

Leave a comment!

  1. Your mail is safe with me. It's only only used to display your Gravatar image!

0 comments

Please feel free to be the first to comment on this page!

By placing a comment you let me know i'm doing a good job. It doesn't have to be constructive, just a "awesome!" makes me really happy! You contribute to a better world!