UPDATE orders SET total_price = total_price * 1.1 WHERE purchase_type = 'online';\nUPDATE orders SET total_price = total_price * 0.8 WHERE purchase_type = 'offline';\nUPDATE orders SET amount = amount * 2 WHERE delivery_type = 'express';<\/code><\/pre>\n\n\n\nThe inefficiency of separate queries comes from the overhead of executing multiple queries, which can impact the overall performance of the database. Each query involves a certain amount of overhead, including parsing, planning, and executing the query, as well as any network latency or disk I\/O that may be involved. In contrast, using a single query to perform multiple updates can reduce this overhead by minimizing the number of queries that need to be executed. Moreover, for each single query the DB engine has to traverse through all the records which impose high workload to the database.<\/p>\n\n\n\n
To accomplish all required modification within a single query, we can use CASE statement, as following :<\/p>\n\n\n\n
UPDATE orders\n SET total_price =\n CASE \n WHEN purchase_type = 'online' THEN total_price * 1.1\n WHEN purchase_type = 'offline' THEN total_price * 0.8\n ELSE total_price\n END,\n amount =\n CASE \n WHEN delivery_type = 'express' THEN amount * 2\n ELSE amount\n END\n WHERE purchase_type IN ('online', 'offline') OR delivery_type = 'express';<\/code><\/pre>\n\n\n\nIn this query, we use the CASE statement to perform different actions based on the value of the purchase_type <\/strong>and delivery_type <\/strong>fields in a single traverse. Using CASE condition we easily increased the total_price<\/strong> of orders with “offline” purchase_type <\/strong>by 10% by multiplying it by 1.1 and for records with “offline” purchase_type<\/strong>, we decreased the total_price by 20% by multiplying it by 0.9, and by using ELSE we don’t make any changes to the total_price field for records where the purchase_type is neither “online” nor “offline”. In the mean while, for records where the delivery_type<\/strong> is “express”, we multiply the amount <\/strong>field by 2. <\/p>\n\n\n\nIt’s important to use a WHERE clause in the UPDATE query not only to ensure that we only modify the records that we intend to and avoid accidentally modifying any other records in the table, but also to eliminate any records that do not need to be updated, which can help minimizing the amount of data that needs to be processed by the query. By minimizing the amount of data that needs to be processed, we can reduce the workload of the database and minimize the amount of time and resources required to complete the query. This can be particularly important for large datasets or complex queries, where the overhead of processing unnecessary data can significantly impact the performance of the database. However, it’s important to note that the efficiency of the WHERE clause also depends on the indexing and optimization strategies used by the database management system.<\/p>\n\n\n\n
I hope you found this post informative and helpful. If you have any thoughts or questions, feel free to leave a comment below\u270c.<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
SQL update statement is an important area in optimizing database queries. Optimizing update queries in large tables can reduce query time and improve efficiency. In … <\/p>\n","protected":false},"author":1,"featured_media":1411,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,215,123,216,35,214],"tags":[212,213,280,40,131,114,211,45,210],"_links":{"self":[{"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/posts\/1397"}],"collection":[{"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/comments?post=1397"}],"version-history":[{"count":2,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/posts\/1397\/revisions"}],"predecessor-version":[{"id":1769,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/posts\/1397\/revisions\/1769"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/media\/1411"}],"wp:attachment":[{"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/media?parent=1397"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/categories?post=1397"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mshaeri.com\/blog\/wp-json\/wp\/v2\/tags?post=1397"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"Complex](\"https:\/\/mshaeri.com\/blog\/wp-content\/uploads\/2023\/03\/image-2.png\")
<\/a>