SQL Dialects Reference/Write queries/Replace query
From Wikibooks, open books for an open world
This page may need to be [edit] Replace query
Replace query inserts new row if no row with such primary key exists or updates existing row if it does. SQL:2003 standard introduced a MERGE statement to implement such functionality, while other implementations provide similar queries named "REPLACE" or so-called "Upsert" query (a portmanteau of UPDATE and INSERT).
| Standard | MERGE statement can be used to do a replace query:
MERGE INTO table_name1 USING table_name2 ON (condition) WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...] WHEN NOT MATCHED THEN INSERT columns VALUES (values) Note that MERGE is much more powerful than just doing replace queries. |
|---|---|
| DB2 | MERGE statement
MERGE INTO phonebook AS p
USING ( VALUES ('john doe', '1234' ) ) AS v(name, extension)
ON ( p.name = v.name )
WHEN MATCHED
UPDATE SET p.extension = v.extension
WHEN NOT MATCHED
INSERT VALUES ( v.name, v.extension )
|
| Firebird | ? |
| Ingres | ? |
| Linter | ? |
| MSSQL | ? |
| MySQL | Allows 3 syntaxes: non-standard REPLACE query, (since 4.1) INSERT ... ON DUPLICATE KEY UPDATE, and a variant on IF EXISTS.
REPLACE [INTO] table [(columns)] VALUES (values) INSERT INTO table (columns) VALUES (values) ON DUPLICATE KEY UPDATE column1=value1, column2=value2 IF EXISTS( SELECT * FROM phonebook WHERE name = 'john doe' ) THEN UPDATE phonebook SET extension = '1234' WHERE name = 'john doe' ELSE INSERT INTO phonebook VALUES( 'john doe','1234' ) END IF |
| Oracle | MERGE statement
MERGE INTO phonebook B
USING (
SELECT name_id
FROM phonebook
WHERE name = 'john doe') E
ON (B.name = E.name)
WHEN MATCHED THEN
UPDATE SET B.extension = '1234'
WHEN NOT MATCHED THEN
INSERT (B.name, B.extension)
VALUES ('john doe', '1234);
IF EXISTS( SELECT * FROM phonebook WHERE name = 'john doe' ) UPDATE phonebook SET extension = '1234' WHERE name = 'john doe' ELSE INSERT INTO phonebook VALUES( 'john doe','1234' ) |
| PostgreSQL | ? |
| SQLite | ? |
| Virtuoso | ? |
