DUPLICATES Clause (duplicates_clause) 

The DUPLICATES clause (duplicates_clause) can be used to determine how key collisions are handled.

Syntax

<duplicates_clause> ::= REJECT DUPLICATES | IGNORE DUPLICATES | UPDATE DUPLICATES

Explanation

The DUPLICATES clause is used in the SQL statements CREATE TABLE statement and INSERT statement.

CREATE TABLE statement

REJECT DUPLICATES or no DUPLICATES clause	The CREATE TABLE statement fails if key collisions occur.
IGNORE DUPLICATES	Any rows that cause key collisions on insertion are ignored.
UPDATE DUPLICATES	Any rows that cause key collisions on insertion overwrite the rows with which they collide.

INSERT statement

If there is already a row in the base table with the key of the row to be inserted, the following cases must be distinguished:

REJECT DUPLICATES or no DUPLICATES clause	The INSERT statement fails.
IGNORE DUPLICATES	The new row is not inserted and processing of the INSERT statement is continued.
UPDATE DUPLICATES	The existing row is overwritten by the new row and processing of the INSERT statement is continued.

If, for an INSERT statement with UPDATE DUPLICATES and a QUERY expression, there is more than one key collision for the same key, it is impossible to predict the content of the respective base table row once the INSERT statement has been completed.

If, for an INSERT statement with IGNORE DUPLICATES and a QUERY expression, more than one row in the result table supplies the same base table key, and if this key did not exist before in the base table, it is impossible to predict which row will be inserted in the table.

If the table name specified in the INSERT statement identifies a table without a user-defined key, the DUPLICATES clause has no effect.