In Part 1 of the tutorial, we have looked at some basic forms for cross-table update in MySQL. In Part 2, we'll introduce some complications into the query - adding conditional criteria in the WHERE clause as well as how to update multiple columns.

3. Conditionally update data in table A based on a common column in table B.

3.1 Here we only want to update Picture column in Categories_Test table where the data in CategoryName column is Seafood in table Categories. Both the join condition and the 'Seafood' category are specified in the WHERE clause.

update categories_test as a, categories as b
set a.Picture=b.Picture
where a.CategoryID=b.CategoryID and b.CategoryName = 'Seafood'

Picture column was updated where CategoryName is Seafood.

3.2 Alternatively, the following SQL does the same thing but used the keyword JOIN explicitly and it contains the join condition. The 'Seafood' category is specified in the WHERE clause.

update categories_test a
inner join categories as b on b.CategoryID=a.CategoryID
set a.Picture=b.Picture
where b.CategoryName = 'Seafood'

3.3 Here is a third way by using EXISTS keyword for a sub-query which checks whether the 'Seafood' category exists.

update categories_test a
set a.Picture = (select b.Picture from Categories b where b.CategoryID = a.CategoryID)
where exists 
(
    select * from categories b
    where b.CategoryID = a.CategoryID and b.CategoryName = 'Seafood'
)

3.4 Here is a fourth way - it correctly updated the Seafood category but with 7 warnings.

update categories_test a
set a.Picture = (select b.Picture 
                 from categories b 
                 where b.CategoryID = a.CategoryID and b.CategoryName = 'Seafood')

4. Update data in table A based on two or more common columns in table B.

4.1 Updates based on two or more common columns are normally used for tables where multiple columns work together as a primary key (known as composite primary key). These columns uniquely identify a record in a table. Please note that query below is used for illustration purpose because CategoryID alone is primary key.

update categories_test as a, categories as b
set a.Picture=b.Picture, a.Description=b.Description
where a.CategoryID=b.CategoryID

Data in Description and Picture column in table Categories_Test were updated - 8 records affected.

4.2 Alternatively, the following SQL does the same thing.

update categories_test a
inner join categories as b on b.CategoryID=a.CategoryID
set a.Picture=b.Picture, a.Description=b.Description

4.3 This one looks awkward and not efficient but it works.

update categories_test a
set a.Picture = (select b.Picture 
                 from categories b 
                 where b.CategoryID = a.CategoryID),
    a.Description = (select b.Description
                 from categories b 
                 where b.CategoryID = a.CategoryID)

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. Using Bulk Insert Statement

7. Basic insert statement

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

9. Combine update and insert into one statement

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

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

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

Back to Tutorial Index Page