{"id":256,"date":"2011-06-08T00:45:57","date_gmt":"2011-06-08T00:45:57","guid":{"rendered":"http:\/\/www.sitekickr.com\/blog\/?p=256"},"modified":"2011-06-08T00:46:18","modified_gmt":"2011-06-08T00:46:18","slug":"mysql-bulk-update-coldfusion","status":"publish","type":"post","link":"https:\/\/www.sitekickr.com\/blog\/mysql-bulk-update-coldfusion\/","title":{"rendered":"MySQL Bulk Update with ColdFusion"},"content":{"rendered":"<p>The example below demonstrates the use of the <a class=\"target-blank\" href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/case-statement.html\">MySQL CASE<\/a> 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.<\/p>\n<p><code>&lt;cfquery&gt;<br \/>\n\t&nbsp;&nbsp;&nbsp; UPDATE mytable<br \/>\n\t&nbsp;&nbsp;&nbsp; SET position = <br \/>\n\t&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; CASE <br \/>\n\t&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &lt;cfloop from=&quot;1&quot; to=&quot;#length#&quot; index=&quot;i&quot;&gt;<br \/>\n\t&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN element_id = #Val(ListGetAt(arguments.positions, i), 2)# THEN #i#<br \/>\n\t&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &lt;\/cfloop&gt;<br \/>\n\t&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; END<br \/>\n\t&lt;\/cfquery&gt;<br \/>\n\t<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"amp_status":""},"categories":[41,13],"tags":[93],"_links":{"self":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/256"}],"collection":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/comments?post=256"}],"version-history":[{"count":3,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/256\/revisions"}],"predecessor-version":[{"id":259,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/256\/revisions\/259"}],"wp:attachment":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/media?parent=256"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/categories?post=256"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/tags?post=256"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}