Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted September 10, 2015

Common MySQL Insert and Update Tasks and Their Solutions

By Rob Gravelle

Even 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
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.

See all articles by Rob Gravelle



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM