INSERT .. ON DUPLICATE KEY UPDATE .. EXCLUDED
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Starting with MySQL 8, there's support for aliasing the INSERT .. VALUES clause in order to reference its columns from the ON DUPLICATE KEY UPDATE clause:
INSERT INTO AUTHOR (ID, LAST_NAME) VALUES (3, 'Koontz'), (4, 'Shakespeare') AS t -- Declare source data alias ON DUPLICATE KEY UPDATE SET last_name = t.last_name -- Reference source data alias
jOOQ does not explicitly support this syntax in the DSL API, but the PostgreSQL specific equivalent from INSERT .. ON CONFLICT .. EXCLUDED can be emulated in MySQL as well, so you can just use the DSL.excluded() pseudo table:
// Add a new author called "Koontz" with ID 3.
// If that ID is already present, update the author's name
create.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME)
.values(3, "Koontz")
.values(4, "Shakespeare")
.onDuplicateKeyUpdate()
.set(AUTHOR.LAST_NAME, excluded(AUTHOR.LAST_NAME))
.execute();
Dialect support
This example using jOOQ:
insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME).values(3, "X").onDuplicateKeyUpdate().set(AUTHOR.LAST_NAME, excluded(AUTHOR.LAST_NAME))
Translates to the following dialect specific expressions:
Aurora MySQL, MariaDB, MemSQL
INSERT INTO AUTHOR (ID, LAST_NAME) VALUES ( 3, 'X' ) ON DUPLICATE KEY UPDATE AUTHOR.LAST_NAME = VALUES(LAST_NAME)
Aurora Postgres, CockroachDB, DuckDB, Postgres, YugabyteDB
INSERT INTO AUTHOR (ID, LAST_NAME) VALUES ( 3, 'X' ) ON CONFLICT (ID) DO UPDATE SET LAST_NAME = excluded.LAST_NAME
DB2
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM SYSIBM.DUAL ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = t.LAST_NAME WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
Derby
MERGE INTO AUTHOR USING SYSIBM.SYSDUMMY1 ON AUTHOR.ID = 3 WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = 'X' WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( 3, 'X' )
Exasol
MERGE INTO AUTHOR USING ( SELECT 3, 'X' ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = t.LAST_NAME WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
Firebird
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM RDB$DATABASE ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = t.LAST_NAME WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
H2
MERGE INTO AUTHOR
USING (
SELECT
3 ID,
'X' LAST_NAME
) t
ON AUTHOR.ID = t.ID
WHEN MATCHED THEN UPDATE SET
AUTHOR.LAST_NAME = t.LAST_NAME
WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME)
VALUES (
t.ID,
t.LAST_NAME
)
Hana
MERGE INTO AUTHOR
USING (
(
SELECT
3 ID,
'X' LAST_NAME
FROM SYS.DUMMY
)
) t
ON AUTHOR.ID = t.ID
WHEN MATCHED THEN UPDATE SET
AUTHOR.LAST_NAME = t.LAST_NAME
WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME)
VALUES (
t.ID,
t.LAST_NAME
)
HSQLDB
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM (VALUES(1)) AS dual(dual) ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = t.LAST_NAME WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
MySQL
INSERT INTO AUTHOR (ID, LAST_NAME) VALUES ( 3, 'X' ) AS t ON DUPLICATE KEY UPDATE AUTHOR.LAST_NAME = t.LAST_NAME
Oracle
MERGE INTO AUTHOR
USING (
(
SELECT
3 ID,
'X' LAST_NAME
)
) t
ON (AUTHOR.ID = t.ID)
WHEN MATCHED THEN UPDATE SET
AUTHOR.LAST_NAME = t.LAST_NAME
WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME)
VALUES (
t.ID,
t.LAST_NAME
)
Snowflake
MERGE INTO AUTHOR USING ( SELECT 3, 'X' ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET LAST_NAME = t.LAST_NAME WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
SQLite
INSERT INTO AUTHOR (ID, LAST_NAME) VALUES ( 3, 'X' ) ON CONFLICT DO UPDATE SET LAST_NAME = excluded.LAST_NAME
SQLServer
MERGE INTO AUTHOR USING ( SELECT 3, 'X' ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = t.LAST_NAME WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME );
Sybase
MERGE INTO AUTHOR USING ( SELECT 3, 'X' FROM SYS.DUMMY ) t (ID, LAST_NAME) ON AUTHOR.ID = t.ID WHEN MATCHED THEN UPDATE SET AUTHOR.LAST_NAME = t.LAST_NAME WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME) VALUES ( t.ID, t.LAST_NAME )
Teradata
MERGE INTO AUTHOR
USING (
SELECT 3, 'X'
FROM (
SELECT 1 AS "dual"
) AS "dual"
) t (ID, LAST_NAME)
ON AUTHOR.ID = t.ID
WHEN MATCHED THEN UPDATE SET
LAST_NAME = t.LAST_NAME
WHEN NOT MATCHED THEN INSERT (ID, LAST_NAME)
VALUES (
t.ID,
t.LAST_NAME
)
ASE, Access, BigQuery, ClickHouse, Informix, Redshift, SQLDataWarehouse, Trino, Vertica
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!