Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 



ON DUPLICATE KEY UPDATE Statement

Description

In a situation in which a duplicate value is inserted into a column for which the UNIQUE index or the PRIMARY KEY constraint has been set, you can update to a new value without outputting the error by specifying the ON DUPLICATE KEY UPDATE clause in the INSERT statement.

However, the ON DUPLICATE KEY UPDATE clause cannot be used in a table in which a trigger for INSERT or UPDATE has been activated, or in a nested INSERT statement.

Syntax

<INSERT … VALUES statement>

<INSERT … SET statement>

<INSERT … SELECT statement>

    INSERT ...

    [ON DUPLICATE KEY UPDATE column_name = expr, ... ]

  • column_name = expr : Specifies the name of the column whose value you want to change next to ON DUPLICATE KEY UPDATE and a new column value by using the equal sign.
Example

--creating a new table having the same schema as a_tbl1

CREATE TABLE a_tbl3 LIKE a_tbl1;

INSERT INTO a_tbl3 SELECT * FROM a_tbl1 WHERE id IS NOT NULL and name IS NOT NULL;

SELECT * FROM a_tbl3;

           id  name                  phone

=========================================================

            1  'aaa'                 '000-0000'

            2  'bbb'                 '000-0000'

            3  'ccc'                 '333-3333'

            6  'eee'                 '000-0000'

 

--insert duplicated value violating UNIQUE constraint

INSERT INTO a_tbl3 VALUES(2, 'bbb', '222-2222');

 

ERROR: Operation would have caused one or more unique constraint violations.

 

--insert duplicated value with specifying ON DUPLICATED KEY UPDATE clause

INSERT INTO a_tbl3 VALUES(2, 'bbb', '222-2222')

ON DUPLICATE KEY UPDATE phone = '222-2222';

 

SELECT * FROM a_tbl3 WHERE id=2;

           id  name                  phone

=========================================================

            2  'bbb'                 '222-2222'