How can I do an insert where not exists?

问题:

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

回答1:

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;

回答2:

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)

回答3:

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

回答4:

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