AboutEven with a lot of experience under your belt as a Database Administrator (DBA), using Insert and Update statements can be a nerve racking affair. I know that every time I run one of these commands on a production database I have to hold my breath as I click the phpMyAdmin GO button. What makes me especially nervous is when the data is coming from another table. This is definitely not the time for an unexpected result! I’d like to share with you some common Insert and Update tasks so that you’ll be better equipped to formulate them when the time comes.
Insert Records Based on Rows from another Table
Unless you are creating dummy test data, your table contents are probably coming from another source, be it user input, data transfer, or from other tables and/or database(s). If that source happens to be other tables within the same database, then MySQL has got you covered. It supports the “INSERT … SELECT” statement, which selects the fields from one table and inserts them into another.
For instance, the following statement will take all the records from table1 and insert them into table2:
INSERT INTO table2 SELECT * FROM table1
If only it were always that simple. In many cases, you might be inserting some of the fields from the source table based on some criteria.
Here’s a table that I added to the public Sakila database just for the purposes of this tutorial:
CREATE TABLE `sakila`.`high_priced_rentals` ( `id` INT NOT NULL AUTO_INCREMENT, `title` VARCHAR(255) NULL, `release_year` YEAR NULL, `rental_rate` DECIMAL(4,2) NULL, PRIMARY KEY (`id`));
As the name suggests, I’d like to store the most expensive rentals in their own table. Forget the fact that it goes against 3NF (normalization) conventions. Let’s just pretend that it’s a performance thing. (It’s done all the time).
Using the INSERT…SELECT statement, we can populate all of the fields except for the id, which is an AUTO_INCREMENT type:
INSERT INTO high_priced_rentals (title, release_year, rental_rate) SELECT title, release_year, rental_rate FROM film WHERE rental_rate > 2.99; 336 row(s) affected Records: 336 Duplicates: 0 Warnings: 0
Once it’s done, we have a table with 336 rows of movies that rent for more than $2.99:
Id title release_year rental_rate 1 ACE GOLDFINGER 2006 4.99 2 AIRPLANE SIERRA 2006 4.99 3 AIRPORT POLLOCK 2006 4.99 4 ALADDIN CALENDAR 2006 4.99 5 ALI FOREVER 2006 4.99
Inserting Only New Rows
Since the primary key is not being inserted in the above example there is nothing stopping duplicate records from being created. The ID will always be unique, but the other fields may or may not be.
That’s where the INSERT IGNORE statement comes in. It will only insert records that are unique. Of course you will have to supply the id value as well or the record will never be a duplicate!
INSERT IGNORE INTO high_priced_rentals (id, title, release_year, rental_rate) SELECT film_id, title, release_year, rental_rate FROM film WHERE rental_rate > 2.99; 0 row(s) affected Records: 336 Duplicates: 336 Warnings: 0
Update Records Based on Rows from another Table
Updating records with data from another table is not quite so simple because there is no UPDATE…SELECT statement. Instead, updates use table joins to connect related rows to each other.
Say that we added another field to the high_priced_rentals table to track restricted (i.e. 18+) movies:
ALTER TABLE `sakila`.`high_priced_rentals` ADD COLUMN `restricted` TINYINT(1) NULL DEFAULT NULL AFTER `rental_rate`;
We could then set the new field’s values to an expression by joining both tables on the film title:
UPDATE high_priced_rentals
INNER JOIN film ON high_priced_rentals.title = film.title
SET restricted = film.rating IN ('R', 'NC-17')
WHERE film.rating IS NOT NULL;
A WHERE clause can be added to further limit updated rows.
A Note about Safe Update Mode
MySql sessions have the safe-updates option set by default. This means that you can't update or delete records without specifying a key (e.g. the primary key) in the WHERE clause.
Attempting to update records without specifying a key will result in an error:
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
The Workbench has a preference setting to toggle safe-updates on the SQL Editor screen. Turn that off if you are certain that a non-indexed field will not yield unpredictable field linking:
SQL Editor
After a reconnection, the above statement populates the high_priced_rentals restricted field with a zero (for false) or one (for true). Here are the contents of the first five rows after the update:
336 row(s) affected Rows matched: 336 Changed: 336 Warnings: 0 Id title release_year rental_rate restricted 1 ACE GOLDFINGER 2006 4.99 0 2 AIRPLANE SIERRA 2006 4.99 0 3 AIRPORT POLLOCK 2006 4.99 1 4 ALADDIN CALENDAR 2006 4.99 1 5 ALI FOREVER 2006 4.99 0
A quick and simple way to satisfy the safe-updates criterion is to put a condition in the WHERE clause on the ID column that is always true, for example:
UPDATE high_priced_rentals
INNER JOIN film ON high_priced_rentals.title = film.title
SET restricted = film.rating IN ('R', 'NC-17')
WHERE high_priced_rentals.id > 0 –-always true!
AND film.rating IS NOT NULL;
For maximum confidence in a one-to-one relationship between table rows, you can’t beat a PK to PK link. All it takes to make that work with our high_priced_rentals update statement is to define the high_priced_rentals id field without the AUTO_INCREMENT attribute during table creation. Doing so would force us to copy over the film table’s film_id along with the other fields as we did in the Inserting Only New Rows section.
Now we can join the two tables like so:
UPDATE high_priced_rentals
INNER JOIN film ON high_priced_rentals.id = film.film_id
SET restricted = film.rating IN ('R', 'NC-17')
WHERE film.rating IS NOT NULL;
Id title release_year rental_rate restricted
2 ACE GOLDFINGER 2006 4.99 0
7 AIRPLANE SIERRA 2006 4.99 0
8 AIRPORT POLLOCK 2006 4.99 1
10 ALADDIN CALENDAR 2006 4.99 1
13 ALI FOREVER 2006 4.99 0
Inserting New Rows and Updating Existing Rows
MySQL has a special ON DUPLICATE KEY UPDATE extension to perform inserts and updates in a single command. It makes adding new data extremely efficient.
INSERT INTO high_priced_rentals (id, title, release_year, rental_rate)
SELECT film_id, title, release_year, rental_rate FROM film
WHERE rental_rate > 2.99
ON DUPLICATE KEY
UPDATE high_priced_rentals.title = film.title,
high_priced_rentals.release_year = film.release_year,
high_priced_rentals.rental_rate = film.rental_rate,
high_priced_rentals.restricted = film.rating IN ('R', 'NC-17');
Conclusion
Inserting and updating table contents with data from other tables and/or views within the same database need not be a nail-biting ordeal. Be sure to familiarize yourself with MySQL’s non-standard statements as well as the ANSI SQL ones to give yourself the best tools for the task at hand.