SQL Dialects Reference/Write queries/Replace query

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). 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. 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 ) 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 MERGE statement (from version SQL Server 2008) DECLARE @UnitMeasureCode nchar(3) = 'ABC' DECLARE @Name varchar(25) = 'Test name' MERGE INTO Production.UnitMeasure AS target USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name) ON (target.UnitMeasureCode = source.UnitMeasureCode) WHEN MATCHED THEN UPDATE SET Name = source.Name WHEN NOT MATCHED THEN INSERT (UnitMeasureCode, Name) VALUES (source.UnitMeasureCode, source.Name) 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); Multi-statement form: 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' ) MERGE statement MERGE INTO phonebook B USING ( SELECT name 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 (name, extension) VALUES ('john doe', '1234); Non-standard simplified form: UPDATE OR INSERT INTO phonebook (name, extension) VALUES ('john doe', '1234') MATCHING (name) REPLACE statement: REPLACE [INTO] table [(columns)] VALUES (values) (always deletes the old row) Since version 9.5 INSERT INTO...ON CONFLICT... syntax inspired by MySQL: INSERT INTO distributors (did, dname) VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc') ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;