REPLACE

REPLACE — Insert or Replace values in a table.

Syntax

REPLACE [INTO]
    [schema.]table_name [ ( column_name [ ,column_name ]... ) ]
    VALUES ( expression [ ,expression]... )
    | SELECT query

Description

Use REPLACE to insert new rows in a table and/or replace existing rows in a table.REPLACE will search a table for a match based on any one of the unique keys defined for that table. A unique key is defined as either a PRIMARY KEY constraint or a UNIQUE constraint (see CREATE TABLE).
If REPLACE does not find a match, it will insert a new row into the table.
If REPLACE finds a match, it will replace that row with the column values provided. If REPLACE finds a match of more than one row, it will replace the first row and it will delete all other matching rows (see Example 4).

The VALUES clause must return a value for every column in the table column_name list in that same order.
REPLACE can be used to merge two tables based on common column values. The table referenced as REPLACE [INTO] is the primary table and must have at least one column defined as a unique key to be used for matching. The second table is defined by the result set of the SELECT query.
If columns referenced by the VALUES clause are not of the same data type as the columns defined in the REPLACE table, automatic data type conversion is attempted.

As the REPLACE statement also removes extra records that match query values, INSERT, UPDATE and DELETE privileges are required for the target table.

Parameters

table_name: Name (optionally schema-qualified) of an existing table.
column_name: Name of one or more columns in table defined by table_name. The column_name list must include the primary key if replacing values in an existing row. If column_name is omitted, the default value is all columns defined by the table table_name, and the VALUES list must include all columns.
expression: A valid expression or value to replace the current column value. There must be an expression provided for each column in the column_name list.
query: A valid SQL query. Refer to the SELECT statement (SELECT) for a description of valid arguments. The result set of the query must return a value for every column in the column_name list, or every column in the table if column_name list is not provided.

Examples

Example 1: Use REPLACE to insert or replace rows with or without column list.

CREATE TABLE table1 (field1 INTEGER PRIMARY KEY,
                     field2 INTEGER);
REPLACE INTO table1 (field1, field2) VALUES (1,2);
SELECT * FROM table1;
 FIELD1  FIELD2
 ------- -------
    1       2
REPLACE INTO table1 VALUES (1,3);
REPLACE INTO table1 VALUES (3,4);
SELECT * FROM table1;
 FIELD1  FIELD2
 ------- -------
    1       3
    3       4

Example 2: Use REPLACE to insert or replace rows in table with multi-column primary key.

CREATE TABLE table5 (field1 INTEGER,
                     field2 INTEGER,
                     field3 STRING,
                     PRIMARY KEY (field1,field2));
INSERT  INTO table5 VALUES (1,2,'abc');
REPLACE INTO table5 VALUES (1,2,'cde');
REPLACE INTO table5 VALUES (2,2,'xyz');
REPLACE INTO table5 VALUES (1,1,'klm');
SELECT * FROM table5;
 FIELD1  FIELD2  FIELD3
 ------- ------- -------
    1       2      cde
    2       2      xyz
    1       1      klm

Example 3: Use REPLACE to merge two tables.

USE HOCKEY
SELECT * FROM hockey;
/* ID column is defined as PRIMARY KEY */
 ID  NUMBER        NAME        POSITION   TEAM
 --- ------- ----------------- --------- ------
  1    37    PATRICE BERGERON   Forward  Bruins
  2    48    CHRIS BOURQUE      Forward  Bruins
  3    11    GREGORY CAMPBELL   Forward  Bruins
...
 10    20    DANIEL PAILLE      Forward  Bruins
 11    49    RICH PEVERLEY      Forward  Bruins
...
 20    54    ADAM MCQUAID       Defense  Bruins
 21    44    DENNIS SEIDENBERG  Defense  Bruins
 22    35    ANTON KHUDOBIN     Goalie   Bruins
 23    40    TUUKKA RASK        Goalie   Bruins
 24     1    MAX SUMMIT         Fan      Bruins

CREATE TABLE stage_hockey (id       INTEGER,
                           number   INTEGER,
                           name     STRING,
                           position STRING,
                           team     STRING);

INSERT INTO stage_hockey VALUES (10,'20','DANIEL PAILLE','Retired','Bruins');
INSERT INTO stage_hockey VALUES (11,'49','RICH PEVERLEY','Retired','Bruins');
INSERT INTO stage_hockey VALUES (25,'66','BOBBY ORR',    'Forward','Bruins');
SELECT * FROM stage_hockey;
 ID  NUMBER       NAME       POSITION   TEAM
 --- ------- --------------- --------- ------
 10    20    DANIEL PAILLE    Retired  Bruins
 11    49    RICH PEVERLEY    Retired  Bruins
 25    66    BOBBY ORR        Forward  Bruins

REPLACE INTO hockey (number, name, position, team)
       SELECT number, name, position, team FROM stage_hockey;

SELECT * FROM stage_hockey;
 ID  NUMBER        NAME        POSITION   TEAM
 --- ------- ----------------- --------- ------
  1    37    PATRICE BERGERON   Forward  Bruins
  2    48    CHRIS BOURQUE      Forward  Bruins
  3    11    GREGORY CAMPBELL   Forward  Bruins
...
 10    20    DANIEL PAILLE      Retired  Bruins   /* Found ID Match, Row Updated */
 11    49    RICH PEVERLEY      Retired  Bruins   /* Found ID Match, Row Updated. */
...
 20    54    ADAM MCQUAID       Defense  Bruins
 21    44    DENNIS SEIDENBERG  Defense  Bruins
 22    35    ANTON KHUDOBIN     Goalie   Bruins
 23    40    TUUKKA RASK        Goalie   Bruins
 24     1    MAX SUMMIT         Fan      Bruins
 25    66    BOBBY ORR          Forward  Bruins   /* No ID Match, Row Inserted */

Example 4: REPLACE finds multiple rows that match.

The last REPLACE statement finds two rows that match:

row with column1 primary key = 1

row with column2 and column3 unique constraint = 22 and 4

It replaces the first row it finds as a match and deletes any other rows it finds as a match.

USE TEST;
CREATE SEQUENCE seq1;
CREATE TABLE testtab (column1 INTEGER PRIMARY KEY
                    , column2 INTEGER
                    , column3 INTEGER DEFAULT (NEXT VALUE FOR seq1)
                    , UNIQUE (column2, column3));

INSERT INTO testtab (column1, column2) VALUES (1,1), (2,2), (3,3);
SELECT * FROM testtab;
 COLUMN1  COLUMN2  COLUMN3
 -------- -------- --------
    1        1        1
    2        2        2
    3        3        3

REPLACE INTO testtab (column1, column2, column3) values (11, 22, DEFAULT);
SELECT * FROM testtab;
 COLUMN1  COLUMN2  COLUMN3
 -------- -------- --------
     1        1       1
     2        2       2
     3        3       3
    11       22       4

REPLACE INTO testtab (column1, column2, column3) values (1, 22, 4);
SELECT * FROM testtab;
 COLUMN1  COLUMN2  COLUMN3
 -------- -------- --------
    1        22       4
    2         2       2
    3         3       3

Example 5: REPLACE behavior with NULL valuese

DROP TABLE IF EXISTS testtab;
CREATE TABLE testtab (col1 INT, col2 INT, col3 STRING, UNIQUE (col1,col2,col3));

REPLACE INTO testtab VALUES (1,1,null);
SELECT inserts, updates, replaces, updatecount FROM system.laststatement;

 INSERTS  UPDATES  REPLACES  UPDATECOUNT
 -------- -------- --------- ------------

    1        0         1          1

SELECT * FROM testtab;

 COL1  COL2   COL3
 ----- ----- ------

   1     1   <null>

REPLACE INTO testtab VALUES (1,1,null);
SELECT inserts, updates, replaces, updatecount FROM system.laststatement;

 INSERTS  UPDATES  REPLACES  UPDATECOUNT
 -------- -------- --------- ------------

    0        0         0          0

SELECT * FROM testtab;

 COL1  COL2   COL3
 ----- ----- ------

   1     1   <null>

REPLACE INTO testtab VALUES (1,1, CASE WHEN 1=1 THEN NULL ELSE 1 END);
SELECT inserts, updates, replaces, updatecount FROM system.laststatement;

 INSERTS  UPDATES  REPLACES  UPDATECOUNT
 -------- -------- --------- ------------

    0        0         0          0

SELECT * FROM testtab;

 COL1  COL2   COL3
 ----- ----- ------

   1     1   <null>

Example 6: Some common REPLACE errors.

REPLACE INTO nosuchtable VALUES (1);
can't find table "NOSUCHTABLE"

REPLACE INTO t1 (f1, f2) VALUES (1);
too few values specified in the value list

REPLACE INTO t1 (f1) VALUES ();
too few values specified in the value list

REPLACE INTO t1 (f2) VALUES (1,2,3);
too many values specified in the value list

REPLACE INTO t1 SELECT 1, 2, 3 FROM DUAL;
too many values specified in the value list

REPLACE INTO t1 (f1, f2) SELECT 1 FROM DUAL;
too few values specified in the value list