MySQL: INSERT Statement

This tutorial explains how to use the MySQL INSERT statement with syntax and examples.

Description

The MySQL INSERT statement is used to insert a single record or multiple records into a table in MySQL.

Syntax

In its simplest form, the syntax for the INSERT statement when inserting a single record using the VALUES keyword in MySQL is:

INSERT INTO table
(column1, column2, ... )
VALUES
(expression1, expression2, ... ),
(expression1, expression2, ... ),
...;

However, the full syntax for the INSERT statement when inserting a single record using the VALUES keyword is:

INSERT [ LOW_PRIORITY | DELAYED | HIGH_PRIORITY ] [ IGNORE ]
INTO table
(column1, column2, ... )
VALUES
(expression1, expression2, ... ),
(expression1, expression2, ... ),
[ ON DUPLICATE KEY UPDATE 
    dup_column1 = dup_expression1,
    dup_column2 = dup_expression2,
    ... ];

Or...

In its simplest form, the syntax for the INSERT statement when inserting multiple records using a sub-select in MySQL is:

INSERT INTO table
(column1, column2, ... )
SELECT expression1, expression2, ...
FROM source_table
[WHERE conditions];

However, the full syntax for the INSERT statement when inserting multiple records using a sub-select is:

INSERT [ LOW_PRIORITY | HIGH_PRIORITY ] [ IGNORE ]
INTO table
(column1, column2, ... )
SELECT expression1, expression2, ...
FROM source_table
[WHERE conditions]
[ ON DUPLICATE KEY UPDATE 
    dup_column1 = dup_expression1,
    dup_column2 = dup_expression2,
    ... ];

Parameters or Arguments

LOW_PRIORITY

Optional. The insert will be delayed until there are no processes reading from the table.

DELAYED

Optional. The inserted rows are put in a buffer until the table is available and the next SQL statement can be issued by the process.

HIGH_PRIORITY

Optional. The insert will be given a higher priority overriding the database's "insert" priorities.

IGNORE

Optional. If specified, all errors encountered during the insert are ignored and treated instead as warnings.

table

The table to insert the records into.

column1, column2

The columns in the table to insert values.

expression1, expression2

The values to assign to the columns in the table. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.

source_table

The source table when inserting data from another table.

WHERE conditions

Optional. The conditions that must be met for the records to be inserted.

ON DUPLICATE KEY UPDATE

Optional. If specified and a row is inserted that would violate a primary key or unique index, an update will be performed instead of an insert. dup_column1 would be assigned the value of dup_expression1, dup_column2 would be assigned the value of dup_expression2, and so on.

Note

• When inserting records into a table using the MySQL INSERT statement, you must provide a value for every NOT NULL column.
• You can omit a column from the MySQL INSERT statement if the column allows NULL values.

Example - Using VALUES keyword

The simplest way to create a MySQL INSERT query to list the values using the VALUES keyword.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(1000, 'Dell');

This MySQL INSERT statement would result in one record being inserted into the suppliers table. This new record would have a supplier_id of 1000 and a supplier_name of 'Dell'.

Example - Using sub-select

You can also create more complicated MySQL INSERT statements using sub-selects.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE customer_id  5000;

By placing a SELECT statement within the INSERT statement, you can perform multiples inserts quickly.

With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by calling the mysql_info function or by running the following MySQL SELECT statement before performing the insert.

SELECT count(*)
FROM customers
WHERE customer_id  5000;