How can I do 'insert if not exists' in MySQL?
Solution 1
Use INSERT IGNORE INTO table
.
There's also INSERT … ON DUPLICATE KEY UPDATE
syntax, and you can find explanations in 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Statement.
Post from bogdan.org.ua according to Google's webcache:
18th October 2007
To start: as of the latest MySQL, syntax presented in the title is not possible. But there are several very easy ways to accomplish what is expected using existing functionality.
There are 3 possible solutions: using INSERT IGNORE, REPLACE, or INSERT … ON DUPLICATE KEY UPDATE.
Imagine we have a table:
CREATE TABLE `transcripts` ( `ensembl_transcript_id` varchar(20) NOT NULL, `transcript_chrom_start` int(10) unsigned NOT NULL, `transcript_chrom_end` int(10) unsigned NOT NULL, PRIMARY KEY (`ensembl_transcript_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Now imagine that we have an automatic pipeline importing transcripts meta-data from Ensembl, and that due to various reasons the pipeline might be broken at any step of execution. Thus, we need to ensure two things:
- repeated executions of the pipeline will not destroy our > database
- repeated executions will not die due to ‘duplicate > primary key’ errors.
Method 1: using REPLACE
It’s very simple:
REPLACE INTO `transcripts` SET `ensembl_transcript_id` = 'ENSORGT00000000001', `transcript_chrom_start` = 12345, `transcript_chrom_end` = 12678;
If the record exists, it will be overwritten; if it does not yet exist, it will be created. However, using this method isn’t efficient for our case: we do not need to overwrite existing records, it’s fine just to skip them.
Method 2: using INSERT IGNORE Also very simple:
INSERT IGNORE INTO `transcripts` SET `ensembl_transcript_id` = 'ENSORGT00000000001', `transcript_chrom_start` = 12345, `transcript_chrom_end` = 12678;
Here, if the ‘ensembl_transcript_id’ is already present in the database, it will be silently skipped (ignored). (To be more precise, here’s a quote from MySQL reference manual: “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.”.) If the record doesn’t yet exist, it will be created.
This second method has several potential weaknesses, including non-abortion of the query in case any other problem occurs (see the manual). Thus it should be used if previously tested without the IGNORE keyword.
Method 3: using INSERT … ON DUPLICATE KEY UPDATE:
Third option is to use
INSERT … ON DUPLICATE KEY UPDATE
syntax, and in the UPDATE part just do nothing do some meaningless (empty) operation, like calculating 0+0 (Geoffray suggests doing the id=id assignment for the MySQL optimization engine to ignore this operation). Advantage of this method is that it only ignores duplicate key events, and still aborts on other errors.As a final notice: this post was inspired by Xaprb. I’d also advise to consult his other post on writing flexible SQL queries.
Solution 2
Solution:
INSERT INTO `table` (`value1`, `value2`)
SELECT 'stuff for value1', 'stuff for value2' FROM DUAL
WHERE NOT EXISTS (SELECT * FROM `table`
WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1)
Explanation:
The innermost query
SELECT * FROM `table`
WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1
used as the WHERE NOT EXISTS
-condition detects if there already exists a row with the data to be inserted. After one row of this kind is found, the query may stop, hence the LIMIT 1
(micro-optimization, may be omitted).
The intermediate query
SELECT 'stuff for value1', 'stuff for value2' FROM DUAL
represents the values to be inserted. DUAL
refers to a special one row, one column table present by default in all Oracle databases (see https://en.wikipedia.org/wiki/DUAL_table). On a MySQL-Server version 5.7.26 I got a valid query when omitting FROM DUAL
, but older versions (like 5.5.60) seem to require the FROM
information. By using WHERE NOT EXISTS
the intermediate query returns an empty result set if the innermost query found matching data.
The outer query
INSERT INTO `table` (`value1`, `value2`)
inserts the data, if any is returned by the intermediate query.
Solution 3
In MySQL, ON DUPLICATE KEY UPDATE or INSERT IGNORE can be viable solutions.
An example of ON DUPLICATE KEY UPDATE update based on mysql.com:
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
UPDATE table SET c=c+1 WHERE a=1;
An example of INSERT IGNORE based on mysql.com
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_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
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 [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Solution 4
Any simple constraint should do the job, if an exception is acceptable. Examples:
- primary key if not surrogate
- unique constraint on a column
- multi-column unique constraint
Sorry if this seems deceptively simple. I know it looks bad confronted to the link you share with us. ;-(
But I nevertheless give this answer, because it seems to fill your need. (If not, it may trigger you updating your requirements, which would be "a Good Thing"(TM) also).
If an insert would break the database unique constraint, an exception is throw at the database level, relayed by the driver. It will certainly stop your script, with a failure. It must be possible in PHP to address that case...
Solution 5
Try the following:
IF (SELECT COUNT(*) FROM beta WHERE name = 'John' > 0)
UPDATE alfa SET c1=(SELECT id FROM beta WHERE name = 'John')
ELSE
BEGIN
INSERT INTO beta (name) VALUES ('John')
INSERT INTO alfa (c1) VALUES (LAST_INSERT_ID())
END
warren
I'm a hobbyist programmer, part-time sysadmin, and full-time analytics, big data, data center management, automation, and cloud computing architect and delivery engineer.
Updated on July 08, 2022Comments
-
warren almost 2 years
I started by googling and found the article How to write INSERT if NOT EXISTS queries in standard SQL which talks about mutex tables.
I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I want to insert does not already exist without using a pair of queries (i.e., one query to check and one to insert is the result set is empty)?
Does a
unique
constraint on a field guarantee theinsert
will fail if it's already there?It seems that with merely a constraint, when I issue the insert via PHP, the script croaks.
-
warren over 14 yearsi added a clarification to the question - does your answer still apply?
-
warren over 14 yearsand can I combine that with "delayed" to speed the script up?
-
knittl over 14 yearsyes, insert delayed might speed up things for you. try it out
-
warren over 14 yearsupdate - I did an
insert ignore delay
and the php script finishes much faster by offloading all of the work onto mysql; for my current purposes, this is perfect; thanks -
CppDude over 14 yearsI believe it does. A unique constraint will cause the failure of incorrect inserts. Note : you have to deal with this failure in your code, but this is quite standard.
-
warren over 14 yearsfor now I'm going to stick with the solution I accepted - but will further look into handling INSERT failures etc as the app grows
-
bobobobo over 13 yearsYes, and keep in mind that REPLACE INTO does DELETE then INSERT, not UPDATE
-
Simon East over 12 years
INSERT IGNORE
basically changes all errors into warnings so that your script is not interrupted. You can then view any warnings with the commandSHOW WARNINGS
. And another important note: UNIQUE constraints don't work with NULL values, ie. row1 (1, NULL) and row2 (1, NULL) will both get inserted (unless another constraint such as a primary key is broken). Unfortunate. -
Eyad Fallatah about 12 yearsPretty expensive if you have a huge load of insertions.
-
Charles Forest about 12 yearstrue, but efficient if you need to add specific checkups
-
Jimmy about 12 yearsSo, which one is best -- INSERT IGNORE INTO table or INSERT … ON DUPLICATE KEY UPDATE ?
-
knittl about 12 years@Jimmy: that depends entirely depends on what you want to achieve, they do different things.
-
Alex V about 12 yearscan you give some more info on how to use this?
-
redolent over 11 years
INSERT … ON DUPLICATE KEY UPDATE
is better since it does not delete the row, preserving anyauto_increment
columns and other data. -
aggregate1166877 about 11 yearsWhere would you put in the
WHERE
statement if you want a specific condition to affect both INSERT and UPDATE? -
knittl about 11 years@user1166877: What
WHERE
condition? There is no where condition in an INSERT statement -
rabudde about 11 yearsThis variant is suitable if no unique key on table exists (
INSERT IGNORE
andINSERT ON DUPLICATE KEY
require unique key constraints) -
Rich about 11 yearsIf you use "from dual" on line 2 instead of "from table", then you don't need the "limit 1" clause.
-
not2qubit over 10 yearsJust to inform everyone. Using
INSERT … ON DUPLICATE KEY UPDATE
method does increment any AUTO_INCREMENT column with failed insert. Probably because it's not really failed, but UPDATE'd. -
Timo Huovinen over 10 years@user1147688 the same happens with
INSERT IGNORE
, this only applies to the InnoDB engine -
Robin almost 10 yearsWhat if
stuff for value1
andstuff for value2
are identical? This would throw aDuplicate column name
-
TheCarver almost 10 yearsGetting odd incremental behaviour using INSERT ON DUPLICATE KEY on InnoDB engine. The auto-increment column increments even on failed inserts. So if you had Keyword1,Keyword2 already stored and then tried to add a new one, Keyword3, Keyword3's ID would actually be incremented by 3, because of the previous duplicate finds/fails.
-
Arth about 8 years@Robin They aren't column names, they are strings; the columns are value1 and value2. This data for different columns of course contain dupes.
-
Arth about 8 yearsI also much prefer
SELECT 1
instead ofSELECT *
in the subqueries. Much more likely that this can be satisfied by an index. -
Hooplator15 over 7 yearsAwesome answer, thank you! I will be using the "on duplicate key update" option.
-
Marcin Owsiany over 7 years@Robin use "AS v1" to solve "duplicate column name", see stackoverflow.com/questions/3164505/…
-
xmedeko about 7 years
REPLACE
may delete the row and then insert instead of update. The side effect is that constraints may delete other objects and delete triggers are fired. -
noonex about 6 yearsInstead of DUAL you can use (at least in mysql) INSERT INTO
table
(value1, value2) SELECT 'stuff for value1', 'stuff for value2' FROM (select 1) x WHERE NOT EXISTS (SELECT * FROMtable
WHERE value1='stuff for value1' AND value2='stuff for value2'); -
mickmackusa over 5 yearsTry This answers are low-value on StackOverflow because they do very little to educate the OP and thousands of future researchers. Please edit this answer to include how the solution works and why it is a good idea.
-
COOLak about 5 yearsAll of that works only if the table has a unique index. There may be reasons to insert rows with the same index, and then this query is useless, it still creates new rows.
-
Ivan Laharnar mink.si about 5 yearsThanks, that's the best solution in this topic, and it works ;-)
-
Omn about 5 years"FROM" is not required for a SELECT statement, you don't need it if you aren't using any columns from that table "FROM DUAL" is pointless
-
Robert Talada almost 5 yearsHow does this advice hold up today? Is "on duplicate key update" still sound advice?
-
warren over 4 yearsWhy would "gaps in your primary keys" - even potentially - "make a programmer mentally unstable"? Gaps occur all the time in primary keys - every time you delete a record, for example.
-
warren over 4 yearsStarting with a
SELECT
defeats the whole purpose of just handing-off a big batch ofINSERT
s and not wanting to worry about duplicates. -
warren over 4 yearsSeveral other answerers have proffered an
INSERT INTO ... SELECT FROM
format. Why did you also? -
Yeti over 4 years@warren Either you did not read my answer, you do not understand it, or I didn't explain it properly. In any case, let me emphasize the following: this is not just a regular
INSERT INTO... SELECT FROM...
solution. Please refer to me a link to an answer that is the same, if you can find it I will delete this answer, otherwise you upvote my answer (deal?). Be sure to verify that the answer you're going to link only uses 1 query (for update+insert), no transaction, and is able to target any combination of columns that are known to be unique (so separately the columns don't need to be unique). -
BurninLeo over 4 yearsFrom the MySQL manual: "REPLACE makes sense only if a table has a PRIMARY KEY or UNIQUE index. Otherwise, it becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another."
-
Leo over 4 yearsPerfect solution in case the to-be-matching fields aren't keys ..!
-
Dharman about 4 yearsWarning:
mysql_*
extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API. -
Ricardo Rivera Nieves over 3 yearsThe INSERT IGNORE INTO tableName works, if the colum you going to affect had a Unique key. That way its now going to insert it into the dbase and going to warn you about it, but will insert the rest.
-
Myoch over 3 yearsis there a way to get the id of existing row after calling INSERT ... ON DUPLICATE KEY UPDATE ? I was using $db->lastInsertId() when using auto_increment, but that's no longer valid if I use this instead, right?
-
knittl over 3 years@Myoch I'm not aware of an option to combine the two.
-
bfontaine almost 3 yearsINSERT IGNORE works but note it ignores ALL errors: stackoverflow.com/a/4920619/735926
-
Janeks Malinovskis over 2 yearsThis example works is a bit wrong. It will insert the duplicate rows as many there exists in the table
meta_key
. If addingLIMIT 1
at the end - it will work, but still this feels kid of unsafe/hacky. I prefer the select part after insert write like this:SELECT * FROM (SELECT DISTINCT ?, ?, ?) as tmp
- it feels safer in case if forgot to writeLIMIT 1
. -
Your Common Sense about 2 yearsWhat's the point in doing mysql_real_escape_string($table)?