Table of Contents for

MySQL in a Nutshell, 2nd Edition

Use this statement to add rows of data to a table. The first format shown can insert only one row of data per statement. The second format can handle one or more rows in a single statement. The columns and their order are specified once, but values for multiple rows may be given. Each row of values is to be contained in its own set of parentheses, separated by commas. The third format inserts columns copied from rows in other tables. Explanations of the specifics of each type of statement, their various clauses and keywords, and examples of their uses follow in the next three subsections of this SQL statement.

A few parameters are common to two formats, and a few are common to all formats.

You can use the LOW_PRIORITY keyword to instruct the server to wait until all other queries related to the table in which data is to be added are finished before running the INSERT statement. When the table is free, it is locked for the INSERT statement and will prevent concurrent inserts.

The DELAYED keyword is available for the first two syntaxes and indicates the same priority status, but it releases the client so that other queries may be run and so that the connection may be terminated. A DELAYED query that returns without an error message does not guarantee that the inserts will take place; it confirms only that the query is received by the server to be processed. If the server crashes, the data additions may not be executed when the server restarts and the user won’t be informed of the failure. To confirm a DELAYED insert, the user must check the table later for the inserted content with a SELECT statement. The DELAYED option works only with MyISAM and InnoDB tables. It’s also not applicable when the ON DUPLICATE KEY UPDATE clause is used.

Use the HIGH_PRIORITY keyword to override a --low-priority-updates server option and to disable concurrent inserts.

The IGNORE keyword instructs the server to ignore any errors encountered and suppress the error messages. In addition, for multiple row insertions, the statement continues to insert rows after encountering errors on previous rows. Warnings are generated that the user can display with the SHOW WARNINGS statement.

The INTO keyword is optional and only for compatibility with other database engines.

The DEFAULT keyword can be given for a column for the first two syntax formats to instruct the server to use the default value for the column. You can set the default value either with the CREATE TABLE statement when the table is created or with the ALTER TABLE statement for existing tables.

The ON DUPLICATE KEY UPDATE clause tells an INSERT statement how to handle an insert when an index in the table already contains a specified value in a column. With this clause, the statement updates the data in the existing row to reflect the new values in the given columns. Without this clause, the statement generates an error. An example appears in the next section.

Single-row insertion with the SET clause

INSERT [LOW_PRIORITY|DELAYED|HIGH_PRIORITY] [IGNORE]
  [INTO] table
  SET column={expression|DEFAULT}, ...
  [ON DUPLICATE KEY UPDATE column=expression, ...]

This variant of the INSERT statement allows only one row of data to be inserted into a table per SQL statement. The SET clause lists one or more column names, each followed by the value to which it is to be set. The value given can be a static value or an expression. Here is an example:

INSERT INTO clients
SET client_name =  'Geoffrey & Company',
city = 'Boston', state = 'MA';

This example lists three columns along with the values to be set in a row entry in the clients table. Other columns in the newly inserted row will be handled in a default manner. For instance, an AUTO_INCREMENT column will be set to the next number in sequence.

As mentioned earlier, the ON DUPLICATE KEY UPDATE clause allows an INSERT statement to handle rows that already contain specified values. Here is an example:

CREATE UNIQUE INDEX client_phone
ON clients(client_name,telephone);

ALTER TABLE clients 
ADD COLUMN new_telephone TINYINT(1) 
AFTER telephone;

INSERT INTO clients
SET client_name = 'Marie & Associates',
new_telephone = 0
telephone = '504-486-1234'
ON DUPLICATE KEY UPDATE
new_client = 1;

This example starts by creating an index on the client_phone column in the clients table. The index type is UNIQUE, which means that duplicate values for the combination of client_name and telephone columns are not allowed. With the second SQL statement, we add a column to flag new telephone numbers for existing clients. The INSERT statement tries to insert the specified client name and telephone number. But it indicates that if there is already a row in the table for the client, a new row is not to be added. Instead, the existing row is to be updated per the UPDATE clause, setting the original entry’s telephone column to the value given in the SET clause. The assumption is that the new data being inserted either is for a new client or is an update to the existing client’s telephone number. Instead of using a column value after the equals sign, a literal value or an expression may be given.

Multiple-row insertions

INSERT [LOW_PRIORITY|DELAYED|HIGH_PRIORITY] [IGNORE]
  [INTO] table [(column,...)]
  VALUES ({expression|DEFAULT},...), (...)
  [ON DUPLICATE KEY UPDATE column=expression,...]

This format of the INSERT statement allows one SQL statement to insert multiple rows. The columns in which data is to be inserted may be given in parentheses in a comma-separated list. If no columns are specified, the statement must include a value for each column in each row, in the order that they appear in the table. In the place reserved for an AUTO_INCREMENT column, specify NULL and the server will insert the correct next value in the column. To specify default values for other columns, use the DEFAULT keyword. NULL may also be given for any other column that permits NULL and that you wish to leave NULL. The VALUES clause lists the values of each row to be inserted into the table. The values for each row are enclosed in parentheses; each row is separated by a comma. Here is an example:

INSERT INTO clients (client_name, telephone)
VALUES('Marie & Associates', '504-486-1234'),
('Geoffrey & Company', '617-522-1234'),
('Kenneth & Partners', '617-523-1234');

In this example, three rows are inserted into the clients table with one SQL statement. Although the table has several columns, only two columns are inserted for each row here. The other columns are set to their default value or to NULL. The order of the values for each row corresponds to the order that the columns are listed.

Normally, if a multiple INSERT statement is entered and one of the rows to be inserted is a duplicate, an error is triggered and an error message is displayed. The statement is terminated and no rows are inserted. The IGNORE keyword, however, instructs the server to ignore any errors encountered, suppress the error messages, and insert only the non-duplicate rows. The results of this statement display like so:

Query OK, 120 rows affected (4.20 sec)
Records: 125  Duplicates: 5  Warnings: 0

These results indicate that 125 records were to be inserted, but only 120 rows were affected or successfully inserted. There were five duplicates in the SQL statement, but there were no warnings because of the IGNORE keyword. Entering the SHOW WARNINGS statement will display the suppressed warning messages.

Inserting rows based on a SELECT

INSERT [LOW_PRIORITY|HIGH_PRIORITY] [IGNORE]
  [INTO] table [(column,...)]
  SELECT...
  [ON DUPLICATE KEY UPDATE column=expression,...]

This method of the INSERT statement allows for multiple rows to be inserted in one SQL statement, based on data retrieved from another table by way of a SELECT statement. If no columns are listed (i.e., an asterisk is given instead), the SELECT will return the values of all columns in the order in which they are in the selected table and will be inserted (if possible without error) in the same order in the table designated for inserting data into. If you don’t want to retrieve all of the columns of the selected table, or if the columns in both tables are not the same, then you must list the columns to retrieve in the SELECT statement and provide a matching ordered list of the columns of the table that data is to be inserted into.

For the following example, suppose that the employees table contains a column called softball to indicate whether an employee is a member of the company’s softball team. Suppose further that it is decided that a new table should be created to store information about members of the softball team and that the team’s captain will have privileges to this new table (softball_team), but no other tables. The employee names and telephone numbers need to be copied into the new table because the team’s captain will not be allowed to do a query on the employees table to extract that information. Here are the SQL statements to set up the new table with its initial data:

CREATE TABLE softball_team
(player_id INT KEY, player_name VARCHAR(50),  
 position VARCHAR(20), telephone CHAR(8));

INSERT INTO softball_team
(player_id, player_name, telephone)
  SELECT emp_id, CONCAT(name_first, ' ', name_last),
  RIGHT(telephone_home, 8)
  FROM employees
  WHERE softball = 'Y';

The first SQL statement creates the new table. The columns are very simple: one column as a row identifier, one column for both the first and last names of the player, another for the player’s home telephone number, and yet another for the player’s position, to be filled in later by the team’s captain. Normally, we wouldn’t include a column like the one for the player’s name because that would be duplicating data in two tables. However, the team captain intends to change many of the player’s names to softball nicknames (e.g., Slugger Johnson).

In the second SQL statement, the INSERT statement uses an embedded SELECT statement to retrieve data from the employees table where the softball column for the row is set to 'Y'. The CONCAT() function is used to put together the first and last names, separated by a space. This will go into the name column in the new table. The RIGHT() function is used to extract only the last eight characters of the telephone_home column because all of the employees on the softball team are from the same telephone dialing area. See Chapter 11 for more information on these functions. Notice that we’ve listed the three columns that data is to go into, although there are four in the table. Also notice that the SELECT statement has three columns of the same data types but with different names.