6.4.3 INSERT Syntax

    INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        VALUES ((expression | DEFAULT),...),(...),...
        [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name
        SET col_name=(expression | DEFAULT), ...
        [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]

INSERT inserts new rows into an existing table. The INSERT ... VALUES form of the statement inserts rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables. The INSERT ... VALUES form with multiple value lists is supported in MySQL Version 3.22.5 or later. The col_name=expression syntax is supported in MySQL Version 3.22.10 or later.

tbl_name is the table into which rows should be inserted. The column name list or the SET clause indicates which columns the statement specifies values for:

• If you specify no column list for INSERT ... VALUES or INSERT ... SELECT, values for all columns in the table must be provided in the VALUES() list or by the SELECT. If you don't know the order of the columns in the table, use DESCRIBE tbl_name to find out.
• Any column not explicitly given a value is set to its default value. For example, if you specify a column list that doesn't name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in section 6.5.3 CREATE TABLE Syntax. You can also use the keyword DEFAULT to set a column to its default value. (New in MySQL 4.0.3.) This makes it easier to write INSERT statements that assign values to all but a few columns, because it allows you to avoid writing an incomplete VALUES() list (a 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. MySQL always has a default value for all fields. This is something that is imposed on MySQL to be able to work with both transactional and non-transactional tables. Our view is that checking of fields content should be done in the application and not in the database server.
• An expression may refer to any column that was set earlier in a value list. For example, you can say this:

  mysql> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
  

  But not this:

  mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);
  

• If you specify the keyword DELAYED, the server puts the row or rows to be inserted into a buffer, and the client issuing the INSERT DELAYED statement then may continue on. If the table is busy, the server holds the rows. When the table becomes free, it begins inserting rows, checking periodically to see if there are new read requests for the table. If there are, the delayed row queue is suspended until the table becomes free again.
• If you specify the keyword LOW_PRIORITY, 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.) See section 6.4.4 INSERT DELAYED Syntax. Note that LOW_PRIORITY should normally not be used with MyISAM tables as this disables concurrent inserts. See section 7.1 MyISAM Tables.
• If you specify the keyword IGNORE in an INSERT with many rows, any rows that duplicate an existing PRIMARY or UNIQUE key in the table are ignored and are not inserted. If you do not specify IGNORE, the insert is aborted if there is any row that duplicates an existing key value. You can determine with the C API function mysql_info() how many rows were inserted into the table.
• If you specify ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0), and a row is inserted that would cause a duplicate value in a PRIMARY or UNIQUE key, an UPDATE of the old row is performed. For example, the command:

  mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
     --> ON DUPLICATE KEY UPDATE c=c+1;
  

  in case of column a is declared as UNIQUE and already holds 1 once, would be identical to the

  mysql> UPDATE table SET c=c+1 WHERE a=1;
  

  Note: that if column b is unique too, the UPDATE command would be written as

  mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
  

  and if a=1 OR b=2 matches several rows, only one row will be updated! In general, one should try to avoid using ON DUPLICATE KEY clause on tables with multiple UNIQUE keys. Since MySQL 4.1.1 one can use function VALUES(col_name) to refer to the column value in the INSERT part of the INSERT ... UPDATE command - that is the value that would be inserted if there would be no duplicate key conflict. This function especially useful in multiple-row inserts. Naturally VALUES() function is only meaningful in INSERT ... UPDATE command and returns NULL otherwise. Example:

  mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
     --> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
  

  The command above is identical to

  mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
     --> ON DUPLICATE KEY UPDATE c=3;
  mysql> INSERT INTO table (a,b,c) VALUES (4,5,6)
     --> ON DUPLICATE KEY UPDATE c=9;
  

  When one uses ON DUPLICATE KEY UPDATE, the DELAYED option is ignored.
• If MySQL was configured using the DONT_USE_DEFAULT_FIELDS option, INSERT statements generate an error unless you explicitly specify values for all columns that require a non-NULL value. See section 2.3.3 Typical configure Options.
• You can find the value used for an AUTO_INCREMENT column with the mysql_insert_id function. See section 9.1.3.31 mysql_insert_id().

If you use INSERT ... SELECT or an INSERT ... VALUES statement with multiple value lists, you can use the C API function mysql_info() to get information about the query. The format of the information string is shown here:

Records: 100 Duplicates: 0 Warnings: 0

Duplicates indicates the number of rows that couldn't 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. The column is set to the default value appropriate for the column type. This is 0 for numeric types, the empty string ('') for string types, and the ``zero'' value for date and time types.
• Setting a numeric column to a value that lies outside the column's range. The value is clipped to the appropriate endpoint of the range.
• Setting a numeric column to a value such as '10.34 a'. The trailing garbage is stripped and the remaining numeric part is inserted. If the value doesn't make sense as a number at all, the column is set to 0.
• Inserting a string into a CHAR, VARCHAR, TEXT, or BLOB column 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 column type. The column is set to the appropriate zero value for the type.

Subsections

• 6.4.3.1 INSERT ... SELECT Syntax

User Comments

This page states that you can use a previously
inserted column to help define a new column, in its
example. However, it seems that auto_increment
columns aren't defined until after the rest of the
query is evaluated. This makes sense, as it wouldn't
assign an auto_increment value until the parser has
verified that the query is valid, but it means that you
can't use an auto_increment column to help define
subsequent columns. For example:

INSERT INTO `table` (id,sentence) VALUES(NULL,
concat('The id is ',id))

Even though the id column is listed first and is
evaluated first, a value is not inserted into it until the
rest of the query is evaluated, so the sentence
column would always contain the string 'The id is 0'.

If you encounter "ERROR 1036: Table 'xxx' is read
only", this may be due to a corrupt internal setting in
MySQL. This can occur if, for example, you upload a
new table from a Windows-based MySQL database
directly to a Linux/Unix database. The solution is to
use 'dumpmysql' to dump the databases, THEN be
sure to DROP TABLE for the 'read only tables' or the
internal config will not be properly reset! Deleting the
table files is not sufficient. Once the tables are
dropped, feed the dump file back to mysql, and it will
create the tables anew, and they will be writable.

Regarding the use of reserved words in table names:
much better than the use of backticks is the
complete avoidance of reserved word usage. The
same goes for spaces in table names (another
scenario which calls for the use of backticks):
generally a bad idea, especially when the use of
backticks could cause potentially cause collision with
their use in scripting language, eg the execution
operator in Perl or PHP (the backtick).

*****************
* COPY A RECORD *
*****************
Ever wanted to copy a table record within
the same table?
Here's how to create a new copy of an existing
record for any kind of table, regardless of the
number of columns or their type.
In this example we have a table called `media` with
a number of fields which we want to duplicate, and
one, ID, containing a unique ID which must
change.
On entry to the PHP (or other language) routine we
know the ID of the record we want to copy
($oldID), and we have generated another ID for the
new record ($newID).

1) Create a TEMPORARY table named
by combining the ID of the record we are to copy
($oldID) and the name of the table. This prevents
collisions if two people are doing the same kind of
thing at the
same time. This new table is populated by the
combined SELECT statement.

2) UPDATE any fields you wish to edit in the
temporary table (in my case, just the field called ID)

3) INSERT the modified record from the new table
back into the old - there's no need to
specify 'WHERE' since this table contains just the one
record we were working with.

4) Drop the old table.

Code:
CREATE TEMPORARY TABLE `media_$oldID`
SELECT * FROM `media`
WHERE ID = '$oldID';

UPDATE `media_$oldID`
SET ID = '$newID';

INSERT INTO `media`
SELECT * FROM `media_$oldID`;

DROP TABLE `media_$oldID`;

Benchmarks:
To create 100 copies of a 360KB media record
(36MB data):
* Windows 98 / PHP / Apache = 20 seconds
* Linux / PHP / Apache (shared hosting server) = 231 seconds - in retrospect, it's probably not a
good idea to performance test a shared web server
without permission ;)

Hope this is of use to someone.

Perhaps it's apparent but since there's not an
example, strings must be quoted when using
VALUES. Otherwise the server evidently interprets
the values as columns since an error message is
generated: "Unknown column 'firstvalue' in 'field
list'".Numbers do not have to be
quoted. Dates may or may not require quoting
based on their format (see "6.2.2.2 The DATETIME,
DATE, and TIMESTAMP Types"). NULLs must be
indicated as "NULL" values, not left blank.

Error 1036 Table tbl_name is read only.
Here is a new one for all you who have recieved this
error running MySql as a service on windows 2000.
When running the MySql server --standalone all
tables work fine, but when installed as a service all
the tables become read-only? After many hours of
troubleshooting i relized when i decided to reload
MySql and tried to DROP a database, that MySql did
not have file write permission to the hard drive under
Win2000. The FIX: go to
start=>programs=>administrative tools=>services
and go to properties of the MySql service (once
installed) select the log-on tab and choose "Log on
as this account: and put in the administrator log-on
information (or a user with file write permissions).
The Local System Account apperentlydoes not seem
to have that permission. Anyway it worked for me if
you should have this problem give it a try.

maybe running a service such as mysql as administrator isn't the best idea in the world, just an opinion (theres a reason it won't run as root generally in linux ;) )

Warning. After moving to 4.0.12 and inodb, an insert can return Query Failed with Lock wait timeout exceeded; where before the process would just wait.

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')

The INSERT ... ON DUPLICATE KEY UPDATE feature is handy, but if you're not ready to upgrade to an non-production version (as is my case), you -may- be able to use REPLACE to accomplish the same thing (based on your code and key usage, of course).

REPLACE is in close proximity to this topic in the manual, but it was a long time before I realized this alternative.

Add your own comment.