MySQL INSERT Statement Variations

In the MySQL Data Manipulation and Query Statements article, we looked at two variations of the INSERT INTO statement. If you recall, we utilized the INSERT statement to populate tables, rather than the Data Manipulation Language (DML) SELECT...INTO command, which is unsupported in MySQL. However, MySQL does provide the INSERT...SELECT statement. This, and other variations of the INSERT statement will be the topic of today’s article.

Inserting Multiple Rows

The INSERT INTO syntax that we used came in two forms – one with column names and one in which they were omitted:

Form 1:

INSERT INTO table_name
VALUES (value1, value2, value3,...)

Form 2:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

Here are some examples of both forms:

INSERT INTO "employees" ("id", "shop_id", "gender", "name", "salary") 
VALUES (1,1,'m','Jon Simpson',4500);
INSERT INTO "employees" VALUES (1,1,'m','Jon Simpson',4500);

The above “VALUES“ syntax can also be used to insert multiple rows by including lists of column values, each enclosed within parentheses and separated by commas. Here’s the syntax for that along with an example:

INSERT INTO table_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
 
INSERT INTO 'employees' 
       ('id', 'shop_id', 'gender', 'name',                 'salary') 
VALUES (1,    1,         'm',      'Jon Simpson',          4500),
       (2,    1,         'f',      'Barbara Breitenmoser', 4700),
       (3,    2,         'f',      'Kirsten Ruegg',        5600),
       (4,    3,         'm',      'Ralph Teller',         5100),
       (5,    3,         'm',      'Peter Jonson',         5200);

Populating Specific Columns

Sometimes, you just want to populate the minimum number of mandatory fields to create a record. The VALUES syntax is not ideally suited for this purpose because the number and ordering of supplied values must exactly match the number of table columns. A better way is to use the SET statement. It allows you to specify the column names you want to populate for one row. Here is what the INSERT INTO...SET syntax looks like:

INSERT INTO table_name 
SET column_name1 ={expr | DEFAULT}[,
    column_name2 ={expr | DEFAULT},
    ...];
 

If a table column is not specified in an INSERT, a couple of things can happen, depending on whether or not strict mode is enabled. In strict mode, an error occurs if any column doesn't have a default value. Otherwise, MySQL uses the implicit default value for any column that does not have an explicitly defined default. The value of course is dependent on the data type. Strings default to an empty string (“”) and integers default to zero (0). Enums default to the first item.

Should both the column list and the VALUES list be empty, INSERT creates a row with each column set according to the above rules.

mysql>INSERT INTO employee () VALUES();
 
mysql>SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)

Here is the employee table with the new row. Notice that the numeric shop_id was defaulted to zero. All the other fields were set to NULL.

id	shop_id	gender	name	salary
1	1	m	Jon Simpson	4500
2	1	f	Barbara Breitenmoser	(NULL)
3	2	f	Kirsten Ruegg	5600
4	3	m	Ralph Teller	5100
5	3	m	Peter Jonson	5200
6	0	(NULL)	(NULL)	(NULL)

The following table definition is similar to the one used in the last article, but with a couple of different column attributes.

• The shop_id field has been changed from NULL to NOT NULL.
• The gender field has also been changed from NULL to NOT NULL and includes a default value.

CREATE TABLE "employees" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "shop_id" INT(11) NOT NULL,
  "gender" enum('m','f') DEFAULT 'm' NOT NULL,
  "name" varchar(32) DEFAULT NULL,
  "salary" int(11) DEFAULT NULL,
  PRIMARY KEY ("id")
);

Use the INSERT INTO...VALUES example above for multiple rows to populate the table.

Here is an example that sets the mandatory shop_id column, using the INSERT INTO...SET statement.

CREATE TABLE "employees" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "shop_id" INT(11) NOT NULL,
  "gender" enum('m','f') DEFAULT 'm' NOT NULL,
  "name" varchar(32) DEFAULT NULL,
  "salary" int(11) DEFAULT NULL,
  PRIMARY KEY ("id")
);

We don’t need to include the row id column because it’s an auto_increment field. The gender will default to its default value of ‘m’, but the remaining two fields will remain NULL as no defaults have been assigned for those.

id	shop_id	gender	name	salary
1	1	M	Jon Simpson	4500
2	1	F	Barbara Breitenmoser	(NULL)
3	2	F	Kirsten Ruegg	5600
4	3	M	Ralph Teller	5100
5	3	M	Peter Jonson	5200
6	2	M	(NULL)	(NULL)

The following statement would fail in strict mode because the shop_id column was omitted and does not have a default value assigned.

mysql>INSERT INTO 'employees' SET name='Jon Simpson';
 
mysql>SQL Error: Field 'shop_id' doesn't have a default value.

Setting Column Values Using a SELECT Statement

There are many instances where it makes more sense to fetch column data from another source, such as another table. The INSERT...SELECT statement serves that purpose by combining the INSERT INTO...VALUES and SELECT statements. Here’s some SQL that populates the employee table with data from another table.

INSERT INTO 'employees' ('shop_id', 'gender', 'name', 'salary') 
SELECT 3,
       LEFT(gender, 1),
       CONCAT_WS(' ', first_name, last_name),
       salary
FROM   transferred_ employees
WHERE  transfer_date > '2008-01-01';

As is often the case with data transfers, some data massaging must be done to make it conform to the structure of the target table. In this case I used the LEFT() function to retrieve the first letter of the source gender field and CONCAT() to build the name column from the first and last name fields. In fact, all of the INSERT statement variations that we’ve looked at here today accept expressions as well as data literals. The expression can be the result of a function or a literal expression such as “3 * 12 – (6 + 1)”.

If you use an INSERT...VALUES statement with multiple value lists or INSERT ... SELECT, the statement returns an information string in this format:

Records: 3 Duplicates: 0 Warnings: 0

Handling Duplicates

MySQL provides a way to deal with duplicate keys using the ON DUPLICATE KEY UPDATE statement. Whenever a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the existing row is performed.

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

Here’s an example to illustrate.

INSERT INTO mytable (primary_id, count) VALUES(5, 1) 
  ON DUPLICATE KEY UPDATE count = count + 1;

In the above example, if the value 5 doesn't exist in the primary_id column, it will be inserted. However, if a value of 5 already exists, an update will be made to the existing column. Note that, in the latter case, affected rows will return 2, which may not be what you would expect.

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT...ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts.

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

If a table contains an auto_increment column and INSERT...ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the auto_increment value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, you can work around this by passing an expression to the LAST_INSERT_ID() function. Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful when an update is performed, use the following code.

INSERT INTO table (email) VALUES (email_address) 
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);

There are other uses for the INSERT statement that you may be interested in, such as importing data from an XML document. The Working with XML Data in MySQL article contains some helpful information on using the INSERT statement for that purpose.

» See All Articles by Columnist Rob Gravelle