[+/-]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO]tbl_name
[(col_name
,...)] {VALUES | VALUE} ({expr
| DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATEcol_name
=expr
[,col_name
=expr
] ... ]
Or:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO]tbl_name
SETcol_name
={expr
| DEFAULT}, ... [ ON DUPLICATE KEY UPDATEcol_name
=expr
[,col_name
=expr
] ... ]
Or:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO]tbl_name
[(col_name
,...)] SELECT ... [ ON DUPLICATE KEY UPDATEcol_name
=expr
[,col_name
=expr
] ... ]
INSERT
inserts 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
Section 12.2.5.1, “INSERT ...
SELECT
Syntax”.
You can use REPLACE
instead of
INSERT
to overwrite old rows.
REPLACE
is the counterpart to
INSERT IGNORE
in
the treatment of new rows that contain unique key values that
duplicate old rows: The new rows are used to replace the old rows
rather than being discarded. See Section 12.2.7, “REPLACE
Syntax”.
tbl_name
is the table into which rows
should be inserted. The columns for which the statement provides
values can be specified as follows:
You can provide a comma-separated list of column names
following the table name. In this case, a value for each named
column must be provided by the VALUES
list
or the SELECT
statement.
If you do not specify a list of column names for
INSERT ...
VALUES
or
INSERT ...
SELECT
, values for every column in the table must be
provided by the VALUES
list or the
SELECT
statement. If you do not
know the order of the columns in the table, use
DESCRIBE
to find out.
tbl_name
The SET
clause indicates the column names
explicitly.
Column values can be given in several ways:
If you are not running in strict SQL mode, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that does not name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in Section 10.1.4, “Data Type Default Values”. See also Section 1.8.6.2, “Constraints on Invalid Data”.
If you want an INSERT
statement
to generate an error unless you explicitly specify values for
all columns that do not have a default value, you should use
strict mode. See Section 5.1.8, “Server SQL Modes”.
Use the keyword DEFAULT
to set a column
explicitly to its default value. This makes it easier to write
INSERT
statements that assign
values to all but a few columns, because it enables you to
avoid writing an incomplete VALUES
list
that does not include a value for each column in the table.
Otherwise, you would have to write out the list of column
names corresponding to each value in the
VALUES
list.
You can also use
DEFAULT(
as a more general form that can be used in expressions to
produce a given column's default value.
col_name
)
If both the column list and the VALUES
list
are empty, INSERT
creates a row
with each column set to its default value:
INSERT INTO tbl_name
() VALUES();
In strict mode, an error occurs if any column doesn't have a default value. Otherwise, MySQL uses the implicit default value for any column that does not have an explicitly defined default.
You can specify an expression expr
to provide a column value. This might involve type conversion
if the type of the expression does not match the type of the
column, and conversion of a given value can result in
different inserted values depending on the data type. For
example, inserting the string '1999.0e-2'
into an INT
,
FLOAT
,
DECIMAL(10,6)
, or
YEAR
column results in the
values 1999
, 19.9921
,
19.992100
, and 1999
being inserted, respectively. The reason the value stored in
the INT
and
YEAR
columns is
1999
is that the string-to-integer
conversion looks only at as much of the initial part of the
string as may be considered a valid integer or year. For the
floating-point and fixed-point columns, the
string-to-floating-point conversion considers the entire
string a valid floating-point value.
An expression expr
can refer to any
column that was set earlier in a value list. For example, you
can do this because the value for col2
refers to col1
, which has previously been
assigned:
INSERT INTO tbl_name
(col1,col2) VALUES(15,col1*2);
But the following is not legal, because the value for
col1
refers to col2
,
which is assigned after col1
:
INSERT INTO tbl_name
(col1,col2) VALUES(col2*2,15);
One exception involves columns that contain
AUTO_INCREMENT
values. Because the
AUTO_INCREMENT
value is generated after
other value assignments, any reference to an
AUTO_INCREMENT
column in the assignment
returns a 0
.
INSERT
statements that use
VALUES
syntax can insert multiple rows. To do
this, include multiple lists of column values, each enclosed
within parentheses and separated by commas. Example:
INSERT INTO tbl_name
(a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
The values list for each row must be enclosed within parentheses. The following statement is illegal because the number of values in the list does not match the number of column names:
INSERT INTO tbl_name
(a,b,c) VALUES(1,2,3,4,5,6,7,8,9);
VALUE
is a synonym for
VALUES
in this context. Neither implies
anything about the number of values lists, and either may be used
whether there is a single values list or multiple lists.
The affected-rows value for an
INSERT
can be obtained using the
ROW_COUNT()
function (see
Section 11.11.3, “Information Functions”), or the
mysql_affected_rows()
C API
function (see Section 20.9.3.1, “mysql_affected_rows()
”).
If you use an INSERT ...
VALUES
statement with multiple value lists or
INSERT ...
SELECT
, the statement returns an information string in
this format:
Records: 100 Duplicates: 0 Warnings: 0
Records
indicates the number of rows processed
by the statement. (This is not necessarily the number of rows
actually inserted because Duplicates
can be
nonzero.) Duplicates
indicates the number of
rows that could not be inserted because they would duplicate some
existing unique index value. Warnings
indicates
the number of attempts to insert column values that were
problematic in some way. Warnings can occur under any of the
following conditions:
Inserting NULL
into a column that has been
declared NOT NULL
. For multiple-row
INSERT
statements or
INSERT INTO ...
SELECT
statements, the column is set to the implicit
default value for the column data type. This is
0
for numeric types, the empty string
(''
) for string types, and the
“zero” value for date and time types.
INSERT INTO ...
SELECT
statements are handled the same way as
multiple-row inserts because the server does not examine the
result set from the SELECT
to
see whether it returns a single row. (For a single-row
INSERT
, no warning occurs when
NULL
is inserted into a NOT
NULL
column. Instead, the statement fails with an
error.)
Setting a numeric column to a value that lies outside the column's range. The value is clipped to the closest endpoint of the range.
Assigning a value such as '10.34 a'
to a
numeric column. The trailing nonnumeric text is stripped off
and the remaining numeric part is inserted. If the string
value has no leading numeric part, the column is set to
0
.
Inserting a string into a string column
(CHAR
,
VARCHAR
,
TEXT
, or
BLOB
) that exceeds the column's
maximum length. The value is truncated to the column's maximum
length.
Inserting a value into a date or time column that is illegal for the data type. The column is set to the appropriate zero value for the type.
If you are using the C API, the information string can be obtained
by invoking the mysql_info()
function. See Section 20.9.3.35, “mysql_info()
”.
If INSERT
inserts a row into a
table that has an AUTO_INCREMENT
column, you
can find the value used for that column by using the SQL
LAST_INSERT_ID()
function. From
within the C API, use the
mysql_insert_id()
function.
However, you should note that the two functions do not always
behave identically. The behavior of
INSERT
statements with respect to
AUTO_INCREMENT
columns is discussed further in
Section 11.11.3, “Information Functions”, and
Section 20.9.3.37, “mysql_insert_id()
”.
The INSERT
statement supports the
following modifiers:
If you use the DELAYED
keyword, the server
puts the row or rows to be inserted into a buffer, and the
client issuing the INSERT
DELAYED
statement can then continue immediately. If
the table is in use, the server holds the rows. When the table
is free, the server begins inserting rows, checking
periodically to see whether there are any new read requests
for the table. If there are, the delayed row queue is
suspended until the table becomes free again. See
Section 12.2.5.2, “INSERT DELAYED
Syntax”.
DELAYED
is ignored with
INSERT ...
SELECT
or
INSERT
... ON DUPLICATE KEY UPDATE
.
DELAYED
is also disregarded for an
INSERT
that uses functions
accessing tables or triggers, or that is called from a
function or a trigger.
If you use 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, and while the INSERT
LOW_PRIORITY
statement is waiting. It is possible,
therefore, 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. (This is in
contrast to INSERT DELAYED
,
which lets the client continue at once. Note that
LOW_PRIORITY
should normally not be used
with MyISAM
tables because doing so
disables concurrent inserts. See
Section 7.3.3, “Concurrent Inserts”.
If you specify 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. See
Section 7.3.3, “Concurrent Inserts”.
LOW_PRIORITY
and
HIGH_PRIORITY
affect only storage engines
that use only table-level locking (such as
MyISAM
, MEMORY
, and
MERGE
).
If you use the IGNORE
keyword, errors that
occur while executing the
INSERT
statement are treated as
warnings instead. For example, without
IGNORE
, a row that duplicates an existing
UNIQUE
index or PRIMARY
KEY
value in the table causes a duplicate-key error
and the statement is aborted. With IGNORE
,
the row still is not inserted, but no error is issued.
IGNORE
has a similar effect on inserts into
partitioned tables where no partition matching a given value
is found. Without IGNORE
, such
INSERT
statements are aborted
with an error; however, when
INSERT
IGNORE
is used, the insert operation fails silently
for the row containing the unmatched value, but any rows that
are matched are inserted. For an example, see
Section 17.2.2, “LIST
Partitioning”.
Data conversions that would trigger errors abort the statement
if IGNORE
is not specified. With
IGNORE
, invalid values are adjusted to the
closest values and inserted; warnings are produced but the
statement does not abort. You can determine with the
mysql_info()
C API function
how many rows were actually inserted into the table.
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
, an UPDATE
of the
old row is performed. The affected-rows value per row is 1 if
the row is inserted as a new row and 2 if an existing row is
updated. See Section 12.2.5.3, “INSERT ... ON
DUPLICATE KEY UPDATE
Syntax”.
Inserting into a table requires the
INSERT
privilege for the table. If
the ON DUPLICATE KEY UPDATE
clause is used and
a duplicate key causes an UPDATE
to
be performed instead, the statement requires the
UPDATE
privilege for the columns to
be updated. For columns that are read but not modified you need
only the SELECT
privilege (such as
for a column referenced only on the right hand side of an
col_name
=expr
assignment in an ON DUPLICATE KEY UPDATE
clause).
User Comments
To insert special characters, like the "apostrophe" read the section on string syntax: http://www.mysql.com/doc/en/String_syntax.html
Here's an example:
insert into Citylist (cityname) VALUES ('St. John\'s')
Please note: "INSERT... ON DUPLICATE KEY UPDATE..." can also use a compound (unique) key to check for duplication. This is very userful. For example:
If you have a log table to log hits to different websites daily, with "site_id"-s and "time" fields, where neither of them are primary keys, but togethether they are unique, then you can create a key on them, and then use "...ON DUPLICATE KEY..."
Table logs:
id: INT(11) auto_increment primary key
site_id: INT(11)
time: DATE
hits: INT(11)
Then:
CREATE UNIQUE INDEX comp ON logs (`site_id`, `time`);
And then you can:
INSERT INTO logs (`site_id`, `time`,`hits`) VALUES (1,"2004-08-09", 15) ON DUPLICATE KEY UPDATE hits=hits+15;
Excellent feature, and it is much faster and briefer then using first a select, then issuing either an update or an insert depending on the value of the select. You also get rid of the probably necessary table-lock during this action.
When using the INSERT ... ON DUPLICATE KEY UPDATE statement, the returned value is as follows:
1 for each successful INSERT.
2 for each successful UPDATE.
For example, if you insert 5 rows with this syntax, and 3 of them were inserted while 2 were updated, the return value would be 7:
((3 inserts * 1) + (2 updates * 2)) = 7.
The return value may at first appear worrisome, as only 5 rows in the table were actually modified, but actually provides more information, because you can determine the quantities of each query type performed from the return value.
For further information, see:
http://bugs.mysql.com/bug.php?id=2709
Fusion des fiches / How to make a file fusion and save it in a new table?
Le code suivant permet de créer une nouvelle table appelée "fusion" avec les champs partition en, classe, segment, F tot, F loc et indice specif.
CREATE TABLE `fusion` (
`partition en` VARCHAR( 11 ) NOT NULL,
`classe` VARCHAR( 11 ) NOT NULL,
`segment` TEXT NOT NULL ,
`F tot` INT NOT NULL ,
`F loc` INT NOT NULL ,
`indice specif` INT NOT NULL
);
On peut mettre à la suite de ce code, le code suivant autant de fois que voulu qui permet de fusionner les tables dans la nouvelle table "fusion":
INSERT INTO l4stal13prema00.`fusion` ( `partition en` ,
`classe` ,
`segment` ,
`F tot` ,
`F loc` ,
`indice specif` )
SELECT *
FROM f3p1
WHERE 1;
INSERT INTO l4stal13prema00.`fusion` ( `partition en` ,
`classe` ,
`segment` ,
`F tot` ,
`F loc` ,
`indice specif` )
SELECT *
FROM f3p2
WHERE 1;
Eviter les répétitions grâce à count(Segment)/ How to avoid REPETITIONS and save it in a new table with COUNT and INSERT ?
http://dev.mysql.com/doc/mysql/en/Counting_rows.html
If you know another way when inserting several files with almost the same data (cat dog turtle + cat dog parrot= cat dog turtle parrot) and avoid repetition, tell it please?
Perhaps it's good to add a reference in the part on the ON DUPLICATE KEY feature to the older REPLACE function, which does a DELETE+INSERT instead of an UPDATE in case of existing key/unique values.
If you do an "INSERT ... ON DUPLICATE KEY UPDATE ..." and neither an insert is possible (because of duplicate keys) nor an update is necessary (because of identical values) you get "2 affected rows" anyway.
If you need plan to get the LAST_INSERT_ID() from a INSERT ... ON DUPLICATE KEY. use ``insert into ... on duplicate key id = LAST_INSERT_ID(id), ...;''
If you do this then SELECT LAST_INSERT_ID() will return either the inserted id or the updated id.
I haven't seen this mentioned elsewhere on this page, but you can use a SELECT statement as a single value if it returns a single value. For example, if we have two tables, t1 and t2:
5 rows in set (0.00 sec)CREATE TABLE t1 (
a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b VARCHAR(10)) TYPE=InnoDB;
INSERT INTO t1 (b) VALUES ('Spike'), ('Chip'), ('John');
CREATE TABLE t2 (
a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b INT NOT NULL,
FOREIGN KEY (b) REFERENCES t1 (a),
c VARCHAR(15)) TYPE=InnoDB;
We can INSERT rows into t2 that populate the foreign key column based on a SELECT statement on t1:
INSERT INTO t2 (b, c)
VALUES ((SELECT a FROM t1 WHERE b='Chip'), 'shoulder'),
((SELECT a FROM t1 WHERE b='Chip'), 'old block'),
((SELECT a FROM t1 WHERE b='John'), 'toilet'),
((SELECT a FROM t1 WHERE b='John'), 'long,silver'),
((SELECT a FROM t1 WHERE b='John'), 'li''l');
Then we get:
mysql> SELECT * FROM t2;
This is especially useful if you don't want to specify the ids for your rows (because they may differ from database to database, due to their being based on AUTO_INCREMENTs), but you want to refer to the values of other tables.
I haven't tested this to determine the version of MySQL this was introduced into, or whether it is necessary that the tables be InnoDB, but it works on my boxes (MySQL 4.1.12)
If you want to add to a tableA a column existing in a tableB:
1) Create an empty column in the tableA:
ALTER TABLE tableA ADD color CHAR(20);
2) If you don't have an auto-incrementation in the two tables (tableB for exemple):
ALTER TABLE tableB ADD (id INT AUTO_INCREMENT NOT NULL, PRIMARY KEY(id));
3) Fill the columns with the values:
UPDATE tableA,tableB SET tableA.color=tableB.color WHERE tableA.id=tableB.id;
If you want to combine insert..select with setting an explicit value for a column - you can use join:
INSERT INTO TargetTable (col1, col2, col3)
SELECT col1,col2,col3
FROM SourceTable JOIN (SELECT 'ExplicitValue' AS col3) AS AnyAlias
This looks quite simple but it took me several hours to understand that there's no need for a special statement to handle such cases.
Regards!
To Jan Jędrzejczyk:
> INSERT INTO TargetTable (col1, col2, col3)
> SELECT col1,col2,col3
> FROM SourceTable
> JOIN (SELECT 'ExplicitValue' AS col3) AS AnyAlias
You could easily do the same thing just by using:
INSERT INTO TargetTable (col1, col2, col3)
SELECT col1,col2, 'ExplicitValue'
FROM SourceTable
hth,
Lokar
I've just discovered that the UPDATE part of the INSERT ... ON DUPLICATE KEY UPDATE syntax doesn't replicate to my slave servers. REPLACE works OK and is only slightly slower. This is with server version 5.0.36sp1-enterprise-gpl-log. I wouldn't recommend anyone use the INSERT ... UPDATE syntax in a replication environment.
If you want to INSERT multiple records using single statement in MS SQL Server, then the syntax for MySQL wont work. But you can use this insert command to accomplish the same:
INSERT INTO tbl_test (FirstName)
SELECT 'Aleem'
UNION ALL
SELECT 'Latif'
UNION ALL
SELECT 'Mughal'
With PHP, I use affected_rows to detect the success of an INSERT IGNORE. This is useful if you need to know whether an INSERT occurred, and is necessary because IGNORE suppresses errors.
PHP code below outputs:
r==1
r==0
Cheers, al.
<?php
$myi = new mysqli("localhost", "user", "pass", "dbname");
$myi->query( <<<SQL_CREATE
create temporary table test_warnings
(
`id_` int(11) NOT NULL,
`num_` int(11) default NULL,
PRIMARY KEY (`id_`)
);
SQL_CREATE
);
$sth=$myi->prepare("insert ignore into test_warnings (id_, num_) values (?,?)");
$id = 9;
$num = 1;
for( $i=0; $i<2; $i++ )
{
$sth->bind_param( "ii", $id, $num );
$sth->execute();
$r = $myi->affected_rows;
print "r==$r\n<br>";
$sth->reset;
}
$sth->close();
?>
I love the examples here from the community, here's what I used to add some recovered backup records to an existing prod table (making sure the old records in prod were deleted first!):
INSERT INTO prod_table
(col1,
col2,
col3
)
SELECT * FROM bkup_table;
Hope this helps somebody.
Add your own comment.