Update Multiple Rows With Different Values With Single Query

1. Overview

In this tutorial, we’ll explore how to update multiple rows with different values, depending on the value of existing columns. To demonstrate the concepts we cover in this tutorial, we’ll be using an example database containing a user table:

+-------------------------------------+
| user_id | name    | age | user_type |
+-------------------------------------+
| 1       | "Bob"   | 42  | NULL      |
| 2       | "Jess"  | 29  | NULL      |
| 3       | "Imran" | 15  | NULL      |
+-------------------------------------+

In the queries we write today, the aim is to UPDATE the user_type to contain “ADULT” for any user records of people 18 or over. Then, “JUNIOR” is used for any records of people under 18, so the final result should look like:

+-------------------------------------+
| user_id | name    | age | user_type |
+-------------------------------------+
| 1       | "Bob"   | 42  | "ADULT"   |
| 2       | "Jess"  | 29  | "ADULT"   |
| 3       | "Imran" | 15  | "JUNIOR   |
+-------------------------------------+

2. Using Multiple Queries

An intuitive way we might approach this problem is by using multiple UPDATE queries:

UPDATE users SET user_type = 'ADULT' WHERE age >= 18;
UPDATE users SET user_type = 'JUNIOR' WHERE age < 18;

These queries use the WHERE clause to determine when to UPDATE a record and set the user_type column to the correct value. While this does work, there are some circumstances where it’s not optimal.

Firstly, we need to ensure these UPDATE queries are executed within a single transaction. Otherwise, it could fail on one UPDATE but not the other, leaving our database in an incomplete state.

Secondly, our example is somewhat trivial. In a real-world example, we may be working on a query requiring different values for many different cases. Using multiple queries in this scenario would be inefficient and require a lot of repetition.

3. Using a Single Query

Now, we understand the potential drawbacks of using multiple UPDATE statements in the scenario. Let’s improve on the SQL code from the previous section to update our user table in a single query.

To do this, we can use a SQL CASE statement to allow us to provide multiple values for a range of conditions, all within a single query:

UPDATE users SET user_type = 
    (CASE 
        WHEN age >= 18 THEN 'ADULT'
        WHEN age < 18 THEN 'JUNIOR'
    END);

As we can see in the snippet above, we’re using a single UPDATE statement. However, when this code sets user_type, the CASE statement returns the different values of “ADULT” or “JUNIOR” based on the value of the age column in the row being updated. This alleviates the concerns of transactions and repetitive code, as mentioned in the previous section. This technique can be used for many different cases, simply by adding a new WHEN clause with the required condition and value.

4. Conclusion

In this article, we’ve covered two ways of solving the problem of updating multiple fields in SQL with different values. Initially, we explored the option of using multiple UPDATE statements. However, we then learned of the potential drawbacks around failures outside of a transaction and repetitiveness.

Finally, we discussed using CASE statements to provide a variety of values depending on given conditions, all in a single query. This solution was optimal as it would safely roll back changes on a failure and keep any repetition to a minimum.