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).

Note: If expression is a CASE statement to evaluates to NULL, there is a known issue where this can cause the REPLACE statement to create a duplicate row (see Example 5).

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.

Parameters

Examples