First, to run the queries on this page, we need to create a new table in our popular MySQL Northwind database.

Create table - Categories_Test

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(200) 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;

Insert some data:

-- Data for the table `categories_test`
insert into `categories_test`(`CategoryID`,`CategoryName`,`Description`,`Picture`) 
values 
(1,'Beverages','Soft drinks, coffees, teas, beers, and ales','beverages.gif'),
(2,'Condiments','Sweet and savory sauces, relishes, spreads, and seasonings','condiments.gif'),
(3,'Confections','Desserts, candies, and sweet breads','confections.gif'),
(4,'Dairy Products','Cheeses','diary.gif'),
(5,'Grains/Cereals','Breads, crackers, pasta, and cereal','cereals.gif'),
(6,'Meat/Poultry','Prepared meats','meat.gif'),
(7,'Produce','Dried fruit and bean curd','produce.gif'),
(8,'Seafood','Seaweed and fish','seafood.gif'),
(9,'Herbs/Spices','Herbal plants and dried spices','herbs.gif'),
(10,'Vegetables','Plant roots, bulbs, leaf vegetables, stems, etc.','vegetables.gif');

Here is the data view after the table is populated.

Our task - change data in the Picture column for records which are not in the Categories table

The Categories_Test table contains two more categories - Herbs/Spices and Vegetables. As they are new categories, we'd like to add a prefix to the filename of their pictures so that we can be easily identified as new categories. The prefix we're going to add is 'new-' (without the single quotes) so the picture names will be changed to below.

• new-herbs.gif
• new-vegetables.gif

Look at the data in table Categories shown below. It's obvious that Categories_Test has two 2 records - CategoryID 9 and 10.

The most common way of adding the prefix is to do a simple update in table Categories_Test for those records whose CategoryID cannot be found in Categories table.

update categories_test
set picture = concat('new-', picture)
where categoryid not in 
(
    select categoryid from categories     
)

The alternative way of adding the prefix is an update with left outer join to find the new categories. As it uses left join, all the records from the table in the left hand side of the JOIN clause (that is the Categories_Test table) are returned in the result. All the matching rows in the right hand side of the JOIN clause (that is the Categories table) are also returned in the result. For the unmatched rows, the data is returned as NULL values in Categories table.

If this does not seem to be straight forward for you, please refer to Using Outer Joins to Combine Data from Two Tables for detailed explanation about using outer joins in SELECT statement.

Here is what the result would look like for the left join before an update is run.

Here is the update query by using left join. We can use b.categoryid is null in the WHERE clause to identify those unmatched rows for new categories that don't exist in Categories table.

update categories_test as a
left join categories as b on b.categoryid=a.categoryid
set a.picture = concat('new-', a.picture)
where b.categoryid is null;

2 records updated:

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. How to do cross-table update in MySQL - Part 1

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