REPLACE INTO
Prev UpSQL CommandsHome Next

REPLACE INTO

REPLACE INTO — update rows in a table while primary key constraint conflict

Synopsis

[ WITH [ RECURSIVE ] with_query [, ...] ]
REPLACE INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER } VALUE ]
    VALUES ( { expression | DEFAULT } [, ...] ) [, ...]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

Description

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY index, the old row is deleted before the new row is inserted.

Note

REPLACE makes sense only if a table has a PRIMARY KEY index (UNIQUE index is not support now). Otherwise, it becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.

Values for all columns are taken from the values specified in the REPLACE statement. Any missing columns are set to their default values, just as happens for INSERT.

You must have both INSERT and UPDATE privileges on a table in order to replace into it.

Parameters

Replacing

This section covers parameters that may be used when only replacing new rows.

with_query

The WITH clause allows you to specify one or more subqueries that can be referenced by name in the REPLACE query. See Section 8.8 and SELECT for details.

table_name

The name (optionally schema-qualified) of an existing table.

alias

A substitute name for table_name. When an alias is provided, it completely hides the actual name of the table.

column_name

The name of a column in the table named by table_name. The column name can be qualified with a subfield name or array subscript, if needed. (replacing into only some fields of a composite column leaves the other fields null.)

OVERRIDING SYSTEM VALUE

If this clause is specified, then any values supplied for identity columns will override the default sequence-generated values.

For an identity column defined as GENERATED ALWAYS, it is an error to insert an explicit value (other than DEFAULT) without specifying either OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE. (For an identity column defined as GENERATED BY DEFAULT, OVERRIDING SYSTEM VALUE is the normal behavior and specifying it does nothing, but LightDB allows it as an extension.)

OVERRIDING USER VALUE

If this clause is specified, then any values supplied for identity columns are ignored and the default sequence-generated values are applied.

This clause is useful for example when copying values between tables. Writing INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM tbl1 will copy from tbl1 all columns that are not identity columns in tbl2 while values for the identity columns in tbl2 will be generated by the sequences associated with tbl2.

expression

An expression or value to assign to the corresponding column.

DEFAULT

The corresponding column will be filled with its default value. An identity column will be filled with a new value generated by the associated sequence. For a generated column, specifying this is permitted but merely specifies the normal behavior of computing the column from its generation expression.

output_expression

An expression to be computed and returned by the REPLACE command after each row is inserted or updated. The expression can use any column names of the table named by table_name. Write * to return all columns of the inserted or updated row(s).

output_name

A name to use for a returned column.

Outputs

On successful completion, an REPLACE command returns a command tag of the form 

REPLACE oid count

The meaning of count and oid is same with INSERT command.

Notes

If the specified table is a partitioned table, each row is routed to the appropriate partition and inserted into it. If the specified table is a partition, an error will occur if one of the input rows violates the partition constraint.

Examples

Consider the table created by the following CREATE TABLE statement: 

CREATE TABLE test (
  id INT NOT NULL,
  name CHAR(16),
  age INT DEFAULT 0,
  PRIMARY KEY(id)
);

When we create this table and run the statements shown in the ltsql client, the result is as follows: 

lightdb@testdb=# REPLACE INTO test VALUES (1, 'Old name', '18');
INSERT 0 1

lightdb@testdb=# REPLACE INTO test VALUES (1, 'New name');
INSERT 0 1

lightdb@testdb=# SELECT * FROM test;
 id |       name       | age 
----+------------------+-----
  1 | New name         |   0
(1 row)

Consider the table created by the following CREATE TABLE statement: 

CREATE TABLE test (
  id INT NOT NULL,
  name CHAR(16),
  seq SERIAL,
  PRIMARY KEY(id)
);

When we create this table and run the statements shown in the ltsql client, the result is as follows, the value of 'seq' becomes 4 because primary key constraint conflict is happened: 

lightdb@testdb=# REPLACE INTO test VALUES (1, 'Old name1'), (2, 'Old name2');
INSERT 0 2

lightdb@testdb=# SELECT * FROM test;
 id |       name       | seq 
----+------------------+-----
  1 | Old name1        |   1
  2 | Old name2        |   2
(2 rows)

lightdb@testdb=# REPLACE INTO test VALUES (1, 'New name');
INSERT 0 1

lightdb@testdb=# SELECT * FROM test;
 id |       name       | seq 
----+------------------+-----
  2 | Old name2        |   2
  1 | New name         |   4
(2 rows)

Compatibility

REPLACE is a LightDB extension to the SQL standard. It either inserts, or deletes and inserts. REPLACE is only supported while the syntax compatible type of the database is mysql. See lightdb_syntax_compatible_type for details.

Prev Up Next
RELEASE SAVEPOINT Home RESET