Update multiple rows (distinctive by primary key) with different values in one query

It is straight-forward to do updates on unique rows with different values by using multiple queries, but there are ways to combine these queries into one single query. Here in this tutorial, we'll demonstrate 3 different ways to write a single update query to update multiple rows with different values.

Suppose we need to update phone numbers for three different customers, we'll normally write 3 queries as shown below.

update customers set Phone='111111' where CustomerID='ALFAA';

update customers set Phone='222222' where CustomerID='CENTC';

update customers set Phone='333333' where CustomerID='FOLIG';

Update query 1:

The following single update query achieves the same effect as the 3 update queries above. Here in the single update query, we use case statement to conditionally update Phone number column for the 3 customers.

update customers set 
    Phone = 
    case CustomerID
       when 'ALFAA' then '111111'
       when 'CENTC' then '222222'
       when 'FOLIG' then '333333'
       else Phone
    end
where CustomerID in ('ALFAA','CENTC','FOLIG');

Update query 2:

The following update query is another way to do the update conditionally. Here the technique implemented is known as cross-table update. We use Table 2 (alias t2) to update values in Table 1 (alias t1) by matching on CustomerID across the 2 tables.

The second table (alias t2) is created by using UNION statement. Data in this table looks just like a normal table (see below), however this table is built on the fly and held in memory rather than in a physical table. To construct the table, we hard-coded actual values for CustomerID and Phone column for each customer, then union them together vertically.

Data in Table 2 for cross-table update

Here is the update query.

UPDATE customers as t1
JOIN 
(
    select 'ALFAA' as CustomerID, '111111' as Phone
    UNION
    select 'CENTC' as CustomerID, '222222' as Phone
    UNION
    select 'FAMIA' as CustomerID, '333333' as Phone
) as t2 on t1.CustomerID=t2.CustomerID
set t1.Phone=t2.Phone;

Update query 3:

In the third update query, we adopted a technique known as INSERT ... ON DUPLICATE KEY UPDATE. It allows us to update an existing row in the table, but if the row does not exist by checking primary key, it'll do INSERT.

INSERT INTO customers_copy(CustomerID,Phone) 
VALUES ('ALFAA','111111'),('CENTC','222222'),('FAMIA','333333')
ON DUPLICATE KEY UPDATE Phone=VALUES(Phone);

Note that if you use later versions of MySQL you may receive a warning as below.

'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead.

This is because we used keyword VALUES which MySQL will deprecate for future versions. As such we rewrote the query by using an alias to replace the VALUES keyword and then use that alias in the ON DUPLICATE KEY UPDATE clause.

INSERT INTO customers(CustomerID, Phone) 
VALUES ('ALFAA','111111'),('CENTC','222222'),('FAMIA','333333') as t1
ON DUPLICATE KEY UPDATE Phone = t1.Phone;

Happy Coding!

Other tutorials in this category

1. Update Statement in MySQL

2. How to update data in a specific order in MySQL

3. How to update top N rows of data in MySQL

4. Use outer join in update

5. How to do cross-table update in MySQL - Part 1

6. How to do cross-table update in MySQL - Part 2

7. Using Bulk Insert Statement

8. Basic insert statement

9. How to create and use AUTO_INCREMENT column and use LAST_INSERT_ID function

10. Combine update and insert into one statement

11. Using MySQL REPLACE (INTO) to mimic DELETE + INSERT

12. Update multiple rows (distinctive by primary key) for multiple columns in one query

Back to Tutorial Index Page

	Custom Search