{"id":2401,"date":"2011-01-27T10:30:24","date_gmt":"2011-01-27T08:30:24","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=2401"},"modified":"2011-01-27T10:30:24","modified_gmt":"2011-01-27T08:30:24","slug":"multi-condition-update-query","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/multi-condition-update-query","title":{"rendered":"Multi condition UPDATE query"},"content":{"rendered":"

A simple question I’ve been asked:<\/p>\n

Is it possible to merge two UPDATE<\/strong> queries, each on different WHERE<\/strong> conditions, into a single query?<\/p>\n

For example, is it possible to merge the following two UPDATE<\/strong> statements into one?<\/p>\n

\n
mysql> UPDATE<\/strong> film SET<\/strong> rental_duration=rental_duration+1 WHERE<\/strong> rating = 'G';\r\nQuery OK, 178 rows affected (0.01 sec)\r\n\r\nmysql> UPDATE<\/strong> film SET<\/strong> rental_rate=rental_rate-0.5 WHERE<\/strong> length < 90;\r\nQuery OK, 320 rows affected (0.01 sec)\r\n<\/pre>\n<\/blockquote>\n
To verify our tests, we take a checksum:<\/p>\n
\n
mysql> pager md5sum\r\nPAGER set to 'md5sum'\r\nmysql> SELECT<\/strong> film_id, title, rental_duration, rental_rate FROM<\/strong> film ORDER BY<\/strong> film_id;\r\nc2d253c3919efaa6d11487b1fd5061f3\u00a0 -\r\n<\/pre>\n<\/blockquote>\n
Obviously, the following query is incorrect<\/strong>:<\/p>\n
\n
mysql> UPDATE<\/strong> film SET<\/strong> rental_duration=rental_duration+1, rental_rate=rental_rate-0.5\u00a0 WHERE<\/strong> rating = 'G' OR<\/strong> length < 90;\r\nQuery OK, 431 rows affected (0.03 sec)\r\n\r\nmysql> pager md5sum\r\nPAGER set to 'md5sum'\r\nmysql> SELECT<\/strong> film_id, title, rental_duration, rental_rate FROM<\/strong> film ORDER BY<\/strong> film_id;\r\n09d450806e2cd7fa78a83ac5bef72d2b\u00a0 -\r\n<\/pre>\n<\/blockquote>\n
Motivation<\/h4>\n
Why would you want to do that?<\/p>\n
    \n
  • While it may seem strange, the merge can be logically (application-wise) perfectly reasonable.<\/li>\n
  • The UPDATE<\/strong> may be time consuming – perhaps it requires full table scan on a large table. Doing it with one scan is faster than two scans.<\/li>\n<\/ul>\n
    The solution<\/h4>\n
    Use a condition for the SET<\/strong> clauses, optionally drop the WHERE<\/strong> conditions.<\/p>\n
    \n
    UPDATE<\/strong>\r\n film\r\nSET<\/strong>\r\n rental_duration=IF<\/strong>(rating = 'G', rental_duration+1, rental_duration),\r\n rental_rate=IF<\/strong>(length < 90, rental_rate-0.5, rental_rate)\r\n;\r\n\r\nmysql> pager md5sum\r\nPAGER set to 'md5sum'\r\nmysql> SELECT<\/strong> film_id, title, rental_duration, rental_rate FROM<\/strong> film ORDER BY<\/strong> film_id;\r\nc2d253c3919efaa6d11487b1fd5061f3\u00a0 -\r\n<\/pre>\n<\/blockquote>\n
    The above query necessarily does a full table scan. If there’s a benefit to using indexes in the WHERE<\/strong> clause, it may still be applied, using an OR<\/strong> condition:<\/p>\n
    \n
    UPDATE<\/strong>\r\n film\r\nSET<\/strong>\r\n rental_duration=IF<\/strong>(rating = 'G', rental_duration+1, rental_duration),\r\n rental_rate=IF<\/strong>(length < 90, rental_rate-0.5, rental_rate)\r\nWHERE<\/strong>\r\n rating = 'G'\r\n OR length < 90\r\n;\r\n<\/pre>\n<\/blockquote>\n
    If there is a computational overhead to the IF<\/strong> statement, I have not noticed it. This kind of solution plays well when each of the distinct queries requires a full scan, on large tables.<\/p>\n","protected":false},"excerpt":{"rendered":"
    A simple question I’ve been asked: Is it possible to merge two UPDATE queries, each on different WHERE conditions, into a single query? For example, is it possible to merge the following two UPDATE statements into one? mysql> UPDATE film SET rental_duration=rental_duration+1 WHERE rating = ‘G’; Query OK, 178 rows affected (0.01 sec) mysql> UPDATE […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[26,21],"class_list":["post-2401","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-indexing","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-CJ","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2401","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=2401"}],"version-history":[{"count":33,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2401\/revisions"}],"predecessor-version":[{"id":3261,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2401\/revisions\/3261"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=2401"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=2401"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=2401"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}