Wikibooks

SQL Dialects Reference/Write queries/Replace query

< SQL Dialects Reference

Replace query edit

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 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)
Ingres ?
Linter ?
MonetDB ?
MSSQL 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)
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);
  • 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' )
PostgreSQL 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;
SQLite REPLACE statement: 
REPLACE [INTO] table [(columns)] VALUES (values)

(always deletes the old row)

Virtuoso ?
Retrieved from "https://en.wikibooks.org/w/index.php?title=SQL_Dialects_Reference/Write_queries/Replace_query&oldid=3677031"