How can I do an insert where not exists?

Question

I'd like to combine an insert query with a "where not exists" so as not to violate PK constraints. However, syntax such as the following gives me an Incorrect syntax near the keyword 'WHERE' error -

INSERT INTO myTable(columns...)
VALUES(values...)
WHERE NOT EXISTS
   (SELECT *
    FROM myTable
    WHERE pk_part1 = value1,
        AND pk_part2 = value2)

How can I accomplish this?

(In general, can you combine an insert with a where clause?)

Is it MySQL. I'm not sure if MySQL will support such sub-query. — Nishant, Jan 16 '11 at 18:06
@Nishant - No, not MySql, there's a SQL tag... I'll add a SQL Server tag for clarity — froadie, Jan 16 '11 at 18:11
You should use a stored procedure and have a look at following link: http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx — Tim Schmelter, Jan 16 '11 at 18:13
@Tim: I added the mysql-error tag because this was originally tagged as mysql, and added the error tag based on the "incorrect syntax" error which is consistent with MySQL. — OMG Ponies, Jan 16 '11 at 18:17
@OMG Ponies - I don't think it was ever tagged as mysql - you can check the original version in the post history — froadie, Jan 16 '11 at 21:20

bernd_k · Accepted Answer · 2011-01-16T19:04:01.337

25

INSERT INTO myTable(columns...)
Select values...
WHERE NOT EXISTS
   (SELECT *
    FROM myTable
    WHERE pk_part1 = value1,
        AND pk_part2 = value2)

Edit: After reading martins link, If admit, that the best solution is:

BEGIN TRY
    INSERT INTO myTable(columns...)
    values( values...)
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
END CATCH;

edited Jan 16 '11 at 19:04

answered Jan 16 '11 at 18:07

bernd_k

11,558
7
45
64

3

This can still lead to attempts to insert duplicates under load. If the OP is on SQL Server 2008 using `Merge` avoids this issue. – Martin Smith Jan 16 '11 at 18:34
3

As described here http://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there – Martin Smith Jan 16 '11 at 18:45
@Martin I guess, I have to rewrite more, than this answer – bernd_k Jan 16 '11 at 18:58
[EAFP](http://www.acronymfinder.com/EAFP.html) or [JFDI](http://www.acronymfinder.com/JFDI.html) – Jonathan Leffler Jan 16 '11 at 19:17
The same question for Oracle seems to be answered in http://stackoverflow.com/questions/3147874/oracle-insert-if-row-not-exists – bernd_k Jan 16 '11 at 19:27
1

In mysql, It does not work without adding (from tableName ) after first select , INSERT INTO myTable(columns...) Select values... from myTable WHERE NOT EXISTS – Yosra Nagati Jul 07 '15 at 10:35

score 3 · Answer 2 · answered Jan 16 '11 at 18:10

3

The simplest way to keep a unique list of values is to either a) set the column(s) as the primary key or b) create a unique constraint on the column(s). Either of these would result in an error when attempting to insert/update values to something that already exists in the table, when a NOT EXISTS/etc would fail silently -- no error, query will execute properly.

That said, use an INSERT/SELECT (don't include the VALUES portion):

INSERT INTO myTable(columns...)
SELECT [statically defined values...]
  FROM ANY_TABLE
 WHERE NOT EXISTS (SELECT NULL
                     FROM myTable
                    WHERE pk_part1 = value1
                      AND pk_part2 = value2)

answered Jan 16 '11 at 18:10

OMG Ponies

325,700
82
523
502

It generates a PK error... I want to avoid that error by checking to make sure it's there first – froadie Jan 16 '11 at 18:13
@froadie: I understand, but I'd be using the error returned to be able to inform the user that the value(s) already exist. – OMG Ponies Jan 16 '11 at 18:16
If the tuple exist then the following error raised: VALUES clause must contain at least one element. Empty elements are not allowed. – Mostafa Barmshory Oct 12 '17 at 07:26

score 0 · Answer 3 · edited Nov 01 '18 at 13:52

0

None of the examples worked for me... so I suggest this example:

INSERT INTO database_name.table_name(column_name)
SELECT column_name
  FROM database_name.table_name
 WHERE NOT EXISTS (SELECT NULL
                     FROM database_name.table_name
                    WHERE column_name = 'Column Value')

edited Nov 01 '18 at 13:52

Sasha Tsukanov

1,025
9
20

answered Nov 01 '18 at 13:32

Tom Stock

21
2

score -1 · Answer 4 · answered Jan 16 '11 at 18:59

mysql has the insert ignore query:

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. 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.

http://dev.mysql.com/doc/refman/5.0/en/insert.html

ON DUPLICATE KEY UPDATE is also available

How can I do an insert where not exists?

4 Answers4

Linked