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   

Limits on syntax

The following syntax is not supported:

  • Syntax that contains PARTITION. The following example shows the syntax:
    REPLACE INTO tb PARTITION (p0) (id) VALUES(7);
  • Syntax where NEXTVAL is nested. The following example shows the syntax:
    REPLACE INTO tb(id) VALUES(SEQ1.NEXTVAL + 1);
  • Syntax that contains column names. The following example shows the syntax:
    REPLACE INTO tb(id1, id2) VALUES(1, id1 + 1);

Limits on distributed transactions

Note If you use table shards, but a transaction is processed in the same database (for example, INSERT or UPDATE contains the shard key), this transaction is considered as a single-database transaction.

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

  • No primary key is specified for the table, as shown in the following example:
    CREATE TABLE tb(id INT, name VARCHAR(10));
    REPLACE INTO tb VALUES(1, 'a');
  • The table is not sharded. The primary key is auto-incremented, but no sequence is used for the primary key. For more information about sequences, see Sequence. The following example shows the corresponding statements:
    CREATE TABLE tb(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10));
    REPLACE INTO tb(name) VALUES('a');

    You can specify a sequence for the primary key to prevent the limit. For more information about sequences, see Sequence. The following example shows the corresponding statements:

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

References

REPLACE syntax for MySQL