INSERT Statement | MariaDB Knowledge Base
Syntax
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col=expr
[, col=expr] ... ] [RETURNING select_expr
[, select_expr ...]]
Or:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [PARTITION (partition_list)]
SET col={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col=expr
[, col=expr] ... ] [RETURNING select_expr
[, select_expr ...]]
Or:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col=expr
[, col=expr] ... ] [RETURNING select_expr
[, select_expr ...]]
Contents
The INSERT statement is used to insert new rows into an existing table. The INSERT ... VALUES
and INSERT ... SET forms of the statement insert rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables. INSERT ... SELECT is discussed further in the INSERT ... SELECT article.
The table name can be specified in the form db_name.tbl_name or, if a default database is selected, in the form tbl_name (see Identifier Qualifiers). This allows to use INSERT ... SELECT to copy rows between different databases.
MariaDB starting with 10.0
The PARTITION clause was introduced in MariaDB 10.0. It can be used in both the INSERT and the SELECT part. See Partition Pruning and Selection for details.
The columns list is optional. It specifies which values are explicitly inserted, and in which order. If this clause is not specified, all values must be explicitly specified, in the same order they are listed in the table definition.
The list of value follow the VALUES or VALUE keyword (which are interchangeable, regardless how much values you want to insert), and is wrapped by parenthesis. The values must be listed in the same order as the columns list. It is possible to specify more than one list to insert more than one rows with a single statement. If many rows are inserted, this is a speed optimization.
For one-row statements, the SET clause may be more simple, because you don't need to remember the columns order. All values are specified in the form col = expr.
Values can also be specified in the form of a SQL expression or subquery. However, the subquery cannot access the same table that is named in the INTO clause.
If you use the LOW_PRIORITY keyword, execution of the INSERT is delayed until no other clients are reading from the table. If you use the HIGH_PRIORITY keyword, the statement has the same priority as SELECTs. This affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE). However, if one of these keywords is specified, concurrent inserts cannot be used. See HIGH_PRIORITY and LOW_PRIORITY clauses for details.
INSERT DELAYED
For more details on the DELAYED option, see INSERT DELAYED.
HIGH PRIORITY and LOW PRIORITY
See HIGH_PRIORITY and LOW_PRIORITY.
Defaults and Duplicate Values
See INSERT - Default & Duplicate Values for details..
INSERT IGNORE
See INSERT IGNORE.
INSERT ON DUPLICATE KEY UPDATE
See INSERT ON DUPLICATE KEY UPDATE.
Examples
Specifying the column names:
INSERT INTO person (first_name, last_name) VALUES ('John', 'Doe');
Inserting more than 1 row at a time:
INSERT INTO tbl_name VALUES (1, "row 1"), (2, "row 2");
Using the SET clause:
INSERT INTO person SET first_name = 'John', last_name = 'Doe';
SELECTing from another table:
INSERT INTO contractor SELECT * FROM person WHERE status = 'c';
See INSERT ON DUPLICATE KEY UPDATE and INSERT IGNORE for further examples.
