DUPLICATES Clause (duplicates_clause)

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

Structure

Syntax Syntax

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

End of the code.

Explanation

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

CREATE TABLE Statement

DUPLICATES Clause (duplicates_clause)
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:

DUPLICATES CLAUSE (duplicates_clause)
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 there is more than one key collision for the same key for an INSERT statement with UPDATE DUPLICATES and QUERY expression specification, 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 produces the same base table key, and if this key did not exist before in the base table, it is impossible to predict the row that 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.