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:
Name | Descriptions |
---|---|
INSERT | Inserts new rows into an existing table. |
LOW_PRIORITY | Using the LOW_PRIORITY keyword, execution of the INSERT is delayed until no other clients are reading from the table. This includes other clients that began reading while existing clients are reading. Therefore it is possible, for a client that issues an INSERT LOW_PRIORITY statement to wait for a very long time (or even forever) in a read-heavy environment. |
DELAYED | Using DELAYED keyword, the server puts the row or rows to be inserted into a buffer, and the INSERT DELAYED statement (issued by the client) can then continue immediately. The server holds the rows if the table is in use. The server begins inserting rows, when the table is free, checking periodically to see whether there are any new read requests for the table. If there are any new read requests, the delayed row queue is suspended until the table becomes free again. |
HIGH_PRIORITY | Using HIGH_PRIORITY, it overrides the effect of the --low-priority-updates option if the server was started with that option. It also causes concurrent inserts not to be used. LOW_PRIORITY and HIGH_PRIORITY affect only storage engines that use only table-level locking such as MyISAM, MEMORY, and MERGE. |
IGNORE | Using the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. |
INTO | Inserts new rows into an existing table. |
tbl_name | Name of the table where rows will be inserted. |
PARTITION | In MySQL 5.6.2 and later, you can control which partitions and subpartitions accept new rows when inserting into a partitioned table. The PARTITION option takes a comma-separated list of the names of one or more partitions or subpartitions (or both) of the table. If any of the rows which are ready to insert, by a given INSERT statement do not match one of the partitions listed, the INSERT statement fails with the error Found a row not matching the given partition set. |
partition_name | Name of the partitioned table(s). |
col_name | A comma-separated list of column names.
|
VALUES | VALUE |
|
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 INTO tb2 (fld_id) SELECT tbl.fld_order_id FROM tbl WHERE tbl.fld_order_id > 200;
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 INTO table (x,y,z) VALUES (1,2,3) ON DUPLICATE KEY UPDATE z=z+1; UPDATE table SET z=z+1 WHERE x=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 INTO newcate
VALUES ("CA006","Sports");
The above statement will insert one row in the table 'newcate'. We have not mentioned any column name here. That is why all of the columns will be affected.
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 INTO newpurchase (invoice_no,ord_no,book_name)
VALUES ("INV001","ORD006",”An advance book of Computer”);
The above statement will insert one(1) row in the table 'newpurchase' for the columns 'invoice_no', 'ord_no', and 'book_name'.
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 INTO newpurchase (invoice_no,ord_no,book_name)
VALUES ("INV002","ORD007",NULL);
The above statement will insert one(1) row in the table 'newpurchase'. Columns 'invoice_no', 'ord_no', and 'book_name' got populated with values where as column 'book_name' got populated with the NULL value.
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 INTO testpurchase
SELECT *
FROM purchase;
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 INTO testpurchase
SELECT *
FROM purchase
WHERE YEAR(invoice_dt)='2008';
The above statement performs the following operations -
- insert rows into 'testpurchase' table from the identical table 'purchase',
- the year of 'invoice_date' of 'purchase' table must be '2008' .
Previous:
MySQL Partitioning
Next:
INSERT records with GROUP BY and ORDER BY
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook