MySQL INSERT statement

INSERT statement

MySQL INSERT statement is used to insert record(s) or row(s) into a table. The insertion of records or rows in the table can be done in two ways, insert a single row at a time, and insert multiple rows at a time.

Version: MySQL 5.6

Syntax:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Arguments:

INSERT ... SELECT

To insert many rows quickly into a table from one or many tables you can use INSERT ... SELECT statement. Here is the syntax :

Syntax:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name 
    [PARTITION (partition_name,...)]
    [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

The following conditions hold for a INSERT ... SELECT statements :

• Using the IGNORE keyword, ignore rows that would cause duplicate-key violations.
• The keyword DELAYED is ignored with INSERT ... SELECT.
• The target table of the INSERT statement may appear in the FROM clause of the SELECT part of the query.
• AUTO_INCREMENT columns work as usual.
• To avoid ambiguous column reference problems when the SELECT and the INSERT refer to the same table.
• In MySQL 5.6.2 and later, you can control which partitions and subpartitions accept new rows when inserting into a partitioned table.

Example:

-- Insert data into table "tb2"
INSERT INTO tb2 (fld_id)
  -- Select "fld_order_id" values from table "tbl" where "fld_order_id" is greater than 200
  SELECT tbl.fld_order_id
  FROM tbl 
  WHERE tbl.fld_order_id > 200;

Explanation:

• INSERT INTO tb2 (fld_id): Specifies that data will be inserted into the "fld_id" column of table "tb2".


• SELECT tbl.fld_order_id: Retrieves values from the "fld_order_id" column of table "tbl".


• FROM tbl: Specifies the source table from which data will be selected.


• WHERE tbl.fld_order_id > 200: Filters the rows from table "tbl" where the value of "fld_order_id" is greater than 200. Only these rows will be inserted into table "tb2".

INSERT DELAYED

The DELAYED option for the INSERT statement is a MySQL extension to standard SQL that can be used for certain kinds of tables (such as MyISAM). When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.

Syntax:

INSERT DELAYED ...

INSERT ... ON DUPLICATE KEY UPDATE

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs a UPDATE of the old row. For example, if column x is declared as UNIQUE and contains the value 1, the following two statements have similar effect:

-- Insert a new row into table "table" with values (1, 2, 3), or update existing row if there is a duplicate key
INSERT INTO table (x, y, z) VALUES (1, 2, 3) 
  ON DUPLICATE KEY UPDATE z=z+1;
-- Update the value of column "z" in table "table" to increment by 1 where the value of column "x" is 1
UPDATE table SET z=z+1 WHERE x=1;

Explanation:

• INSERT INTO table (x, y, z) VALUES (1, 2, 3): Inserts a new row into the table named "table" with values (1, 2, 3) for columns "x", "y", and "z" respectively.


• ON DUPLICATE KEY UPDATE z=z+1: If there is a duplicate key conflict on the primary key or unique index, this statement updates the value of column "z" by incrementing it by 1.


• UPDATE table SET z=z+1 WHERE x=1: Updates the value of column "z" in the table named "table" where the value of column "x" is equal to 1, incrementing it by 1.

Note: The effects are not identical for an InnoDB table where a is an auto-increment column. With an auto-increment column, an INSERT statement increases the auto-increment value but UPDATE does not.

Examples:

MySQL INSERT INTO statement is used to insert record(s) or row(s) into a table.

Syntax:

INSERT INTO table_name ([column_name],[...column_name],...)
VALUES( [column_value],[..column_value]);

Example with PHP code to insert data into a MySQL table

HTML code (say form.html):

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>form to insert data</title>
</head>
<body>
<form method="post" action="insert.php">
<input type="text" name="name" />
<input type="text" name="email" />
<input type="text" name="address" />
<input type="submit" value="Submit">
</form>
</body>
</html>

PHP code (say insert.php):

<?php
$host="localhost"; //yout host name
$username="root";  //yout user name
$password="";      // your password
$db_name="test";  // your database name
$con=MySQL_connect("$host", "$username", "$password")or die("cannot connect"); //MySQL connection
MySQL_select_db("$db_name")or die("can not select DB"); //select your database
$name = $_POST['name'];
$email = $_POST['email'];
$address = $_POST['address'];
$query = "INSERT INTO test (name,email,address) VALUES ('$name', '$email', '$address')";
MySQL_query($query) or die('Query "' . $query . '" failed: ' . MySQL_error());
// name, email and address are fields of your fields; test your table. $name, $email and $address are values collected from the form
?>

MySQL: INSERT one row in a table

The following statement inserts a single row into a table using MySQL INSERT INTO statement.

Code:

-- Insert a new row into the table named "newcate" with specific values
INSERT INTO newcate 
VALUES ("CA006","Sports");

Explanation:

• INSERT INTO newcate: Specifies that data will be inserted into the table named "newcate".


• VALUES ("CA006","Sports"): Specifies the values to be inserted into each column of the table in the order they appear in the table definition. In this case, it inserts the value "CA006" into the first column and "Sports" into the second column. If the table has more columns, they need to be provided in the same order.

mysql> select * from newcate;
+---------+--------------+
| cate_id | cate_descrip |
+---------+--------------+
| CA001   | Science      | 
| CA002   | Technology   | 
| CA003   | Computers    | 
| CA004   | Nature       | 
| CA005   | Medical      | 
| CA006   | Sports       | 
+---------+--------------+
6 rows in set (0.00 sec)

MySQL: INSERT values for specific columns

The following statement inserts values for specific columns using MySQL INSERT INTO statement.

Sample table: newpurchase

Code:

-- Insert a new row into the table named "newpurchase" with specific values for selected columns
INSERT INTO newpurchase (invoice_no, ord_no, book_name)
-- Specify the values to be inserted into the specified columns
VALUES ("INV001", "ORD006", "An advance book of Computer");

Explanation:

• INSERT INTO newpurchase: Specifies that data will be inserted into the table named "newpurchase".


• (invoice_no, ord_no, book_name): Specifies the columns into which data will be inserted. Only these columns will be populated with values.


• VALUES: Keyword indicating that specific values will be provided for the specified columns.


• ("INV001", "ORD006", "An advance book of Computer"): Values to be inserted into the respective columns. "INV001" will be inserted into the "invoice_no" column, "ORD006" into the "ord_no" column, and "An advance book of Computer" into the "book_name" column.

MySQL: INSERT NULL values

The following statement inserts NULL values into one or more columns using MySQL INSERT INTO statement.

Sample table: newpurchase

Code:

-- Insert a new row into the table named "newpurchase" with specific values for selected columns
INSERT INTO newpurchase (invoice_no, ord_no, book_name) 
-- Specify the values to be inserted into the specified columns
VALUES ("INV002", "ORD007", NULL);

Explanation:

• INSERT INTO newpurchase: Indicates that data will be inserted into the table named "newpurchase".


• (invoice_no, ord_no, book_name): Specifies the columns into which data will be inserted. Only these columns will be populated with values.
• VALUES: Keyword indicating that specific values will be provided for the specified columns.
• ("INV002", "ORD007", NULL): Values to be inserted into the respective columns. "INV002" will be inserted into the "invoice_no" column, "ORD007" into the "ord_no" column, and NULL into the "book_name" column, indicating that there is no book name for this purchase.

Inserting multiple rows in a single SQL query

In MySQL, you can insert multiple rows in a single SQL query. Here is the syntax:

INSERT INTO Table ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 );

MySQL INSERT rows with SELECT statement

The following statement inserts values into a table using MySQL INSERT INTO statement when the column names and values are collected from another identical table using MySQL SELECT statement. This way you can insert values of one table into another when tables are identical.

Sample table : purchase

Code:

-- Insert data into the table named "testpurchase" by selecting all rows from the table named "purchase"
INSERT INTO testpurchase 
-- Select all columns (*) from the table "purchase"
SELECT * 
-- Specify the source table from which data will be selected
FROM purchase;

Explanation:

• INSERT INTO testpurchase: Specifies that data will be inserted into the table named "testpurchase".


• SELECT * FROM purchase: Retrieves all columns and rows from the table named "purchase".


• The combination of INSERT INTO ... SELECT * FROM ... allows you to copy data from one table to another. In this case, all rows and columns from the "purchase" table are copied into the "testpurchase" table.

MySQL INSERT rows with SELECT statement and WHERE

The following statement inserts values into a table using MySQL INSERT INTO statement when the column names and values are collected from another identical table using MySQL SELECT and WHERE. This way you can insert values based upon some conditions of one table into another when tables are identical.

Sample table: purchase

Code:

-- Insert data into the table named "testpurchase" by selecting specific rows from the table named "purchase"
INSERT INTO testpurchase 
-- Select all columns (*) from the table "purchase"
SELECT * 
-- Specify the source table from which data will be selected
FROM purchase 
-- Filter the rows from the "purchase" table based on the year of the invoice date
WHERE YEAR(invoice_dt) = '2008';

Explanation:

• INSERT INTO testpurchase: Specifies that data will be inserted into the table named "testpurchase".


• SELECT * FROM purchase: Retrieves all columns and rows from the table named "purchase".


• WHERE YEAR(invoice_dt) = '2008': Filters the rows from the "purchase" table based on the year extracted from the "invoice_dt" column. Only rows with an invoice date in the year 2008 will be selected for insertion into the "testpurchase" table.

Previous: MySQL Partitioning
Next: INSERT records with GROUP BY and ORDER BY