Complex Update In A Single SQL Query Using CASE Statements

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 this post, I will describe how to combine multiple update queries into a single query using the CASE condition to improve the execution time and reduce workload of the database.

Assume we have an “order” table with fields id, item, purchase_type, delivery_type, amount and total_price :

And we need to accomplish following modification on the table’s records :

1- Increasing total_price of orders which their purchase_type equals to “online” by 10%.
2- Decreasing total_price of orders which their purchase_type equals to “offline” by 20%.
3- Multiplying the value of amount field by 2 in records which have delivery_type equals to “express”.

Accomplishing these 3 tasks with separate queries can be the simplest but most inefficient choice :

UPDATE orders SET total_price = total_price * 1.1 WHERE purchase_type = 'online';
UPDATE orders SET total_price = total_price * 0.8 WHERE purchase_type = 'offline';
UPDATE orders SET amount = amount * 2 WHERE delivery_type = 'express';

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

To accomplish all required modification within a single query, we can use CASE statement, as following :

UPDATE orders
 SET total_price =
     CASE 
         WHEN purchase_type = 'online' THEN total_price * 1.1
         WHEN purchase_type = 'offline' THEN total_price * 0.8
         ELSE total_price
     END,
     amount =
     CASE 
         WHEN delivery_type = 'express' THEN amount * 2
         ELSE amount
     END
 WHERE purchase_type IN ('online', 'offline') OR delivery_type = 'express';

In this query, we use the CASE statement to perform different actions based on the value of the purchase_type and delivery_type fields in a single traverse. Using CASE condition we easily increased the total_price of orders with “offline” purchase_type by 10% by multiplying it by 1.1 and for records with “offline” purchase_type, 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 is “express”, we multiply the amount field by 2.

It’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.

I hope you found this post informative and helpful. If you have any thoughts or questions, feel free to leave a comment below✌.