MySQL can use either standard or non-standard (non ANSI standard) SQL syntax format to perform cross-table update.

In this tutorial, we are going to look at four scenarios for MySQL cross table update.

Suppose we have two tables Categories and Categories_Test. See screenshots below.

The common column in the two tables is CategoryID. We are going to update column Description and Picture in table Categories_Test by using data in table Categories, based on data in the common column CategoryID.

Table: Categories

Table: Categories_Test

Use the following script to create the categories_test table in MySQL Northwind database.

CREATE TABLE `categories_test` 
(
  `CategoryID` tinyint(5) unsigned NOT NULL AUTO_INCREMENT,
  `CategoryName` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `Description` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `Picture` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`CategoryID`),
  UNIQUE KEY `Uidx_categories_category_name` (`CategoryName`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
 
-- Data for the table `categories_test`
insert into `categories_test`(`CategoryID`,`CategoryName`,`Description`,`Picture`) 
values 
(1,'Beverages','',''),
(2,'Condiments','',''),
(3,'Confections','',''),
(4,'Dairy Products','',''),
(5,'Grains/Cereals','',''),
(6,'Meat/Poultry','',''),
(7,'Produce','',''),
(8,'Seafood','','');

To repeatedly run the queries in this tutorial, use the following query to empty the data in the Description and Picture column after each update.

update categories_test
set description='',picture=''

1. Update data in a column in table A based on a common column in table B.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

1.1 The update query below shows that the Picture column is updated by looking up the same ID value in CategoryID column in table Categories_Test and Categories. The join condition is specified in the WHERE clause.

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

In Categories_test table, picture column was updated for 8 records.

1.2 SQL below is equivalent to the one shown above. The difference is that it explicitly uses the keyword JOIN in the script. Also note that the join condition is specified in the INNER JOIN clause.

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

1.3 Here is a third way to do the same update. It uses the Oracle way of doing cross-table update. Note that the join condition is specified in the sub-query's WHERE clause.

update Categories_Test a
set a.Picture = (select b.Picture from Categories b where b.CategoryID = a.CategoryID)

2. Update data in two columns in table A based on a common column in table B.

2.1 When updating multiple columns, use comma to separate each column after the SET keyword. The join condition is specified in the WHERE clause.

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

In Categories_test table, both Description and Picture column were updated for 8 records.

2.2 Alternatively, the following SQL does the same thing but used the keyword JOIN explicitly and the join condition is specified in the INNER JOIN clause.

update categories_test a
inner join categories as b on b.categoryid=a.categoryid
set a.picture=b.picture, a.Description=b.Description

2.3 Here is a third way to do cross-table update for two columns. Note that the join condition is specified in the two sub-queries' WHERE clause. Performance wise it's 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)

Click here to view Part 2 of this tutorial.

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.

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 2

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