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.