REPLACE-PolarDB(PolarDB)-阿里云帮助中心

You can use the REPLACE syntax to insert rows to tables or replace rows in tables.

Syntax

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] [schema_name.]tbl_name
[(col_name [, col_name] ...)]
{VALUES | VALUE} (value_list) [, (value_list)]

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] [schema_name.]tbl_name
SET assignment_list

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] [schema_name.]tbl_name
[(col_name [, col_name] ...)]
SELECT ...

value_list:
    value [, value] ...

value:
    {expr | DEFAULT}

assignment_list:
    assignment [, assignment] ...

assignment:
    col_name = value

Unsupported syntax

The following syntax forms are not supported:

PARTITION clause:

REPLACE INTO tb PARTITION (p0) (id) VALUES(7);

Nested NEXTVAL:

REPLACE INTO tb(id) VALUES(SEQ1.NEXTVAL + 1);

Column name references in VALUES:

REPLACE INTO tb(id1, id2) VALUES(1, id1 + 1);

Limitations when distributed transactions are enabled

Note If you use table shards but a transaction targets only one database (for example, an INSERT or UPDATE that includes the shard key), it is treated as a single-database transaction.

When the distributed transaction feature is enabled, REPLACE is not supported in the following cases:

No primary key defined:

CREATE TABLE tb(id INT, name VARCHAR(10));
REPLACE INTO tb VALUES(1, 'a');  -- Not supported

Unsharded table with an auto-increment primary key and no sequence:

CREATE TABLE tb(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10));
REPLACE INTO tb(name) VALUES('a');  -- Not supported

To use REPLACE on an unsharded table with an auto-increment primary key, assign a BY GROUP sequence to the primary key:

CREATE TABLE tb(id INT PRIMARY KEY AUTO_INCREMENT BY GROUP, name VARCHAR(10));
REPLACE INTO tb(name) VALUES('a');  -- Supported

References

REPLACE syntax for MySQL