Suppose we need to update phone numbers and/or fax numbers for four different customers, we'll normally write 4 separate queries as below.

Please note the differences to the queries in Part 1. Both phone and fax number are updated for customer 'ALFAA'. Customer 'CENTC' and 'FOLIG' only have phone numbers got updated. Customer 'LINOD' only has fax number got updated.

update customers set Phone='111111', Fax='444444' where CustomerID='ALFAA';
update customers set Phone='222222' where CustomerID='CENTC';
update customers set Phone='333333' where CustomerID='FOLIG';
update customers set Fax='555555' where CustomerID='LINOD';

Update query 1:

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

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

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.

Please note that for customer 'CENTC' and 'FOLIG', fax numbers are not updated in the original update queries. Therefore, we shouldn't change their values, as such we copied the actual fax numbers from customers table and hardcoded them in the second and third union statement. Similarly, we copied phone number for customer 'LINOD' and hardcoded it in the last union statement.

Here is the update query.

UPDATE customers as t1
JOIN 
(
     select 'ALFAA' as CustomerID, '111111' as Phone, '444444' as Fax
     UNION
     select 'CENTC' as CustomerID, '222222' as Phone, '(5) 555-7293' as Fax
     UNION
     select 'FOLIG' as CustomerID, '333333' as Phone, '20.16.10.17' as Fax
     UNION
     select 'LINOD' as CustomerID, '(8) 34-56-12' as Phone, '555555' as Fax
) as t2 on t1.CustomerID=t2.CustomerID
set t1.Phone=t2.Phone, t1.Fax=t2.Fax;

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(CustomerID,Phone,Fax) 
VALUES ('ALFAA','111111','444444'),('CENTC','222222','(5) 555-7293'),
('FAMIA','333333','20.16.10.17'),('LINOD','(8) 34-56-12','555555')
ON DUPLICATE KEY UPDATE Phone=VALUES(Phone),Fax=VALUES(Fax);

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,Fax) 
VALUES ('ALFAA','111111','444444'),('CENTC','222222','(5) 555-7293'),
('FAMIA','333333','20.16.10.17'),('LINOD','(8) 34-56-12','555555') 
as t1(CustomerID,Phone,Fax)
ON DUPLICATE KEY UPDATE Phone=t1.Phone,Fax=t1.Fax;

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) with different values in one query

Back to Tutorial Index Page