REPLACE

The REPLACE statement is semantically a combined DELETE+INSERT statement. It can be used to simplify application code.

Synopsis

ReplaceIntoStmt

REPLACE - 图1

PriorityOpt

REPLACE - 图2

IntoOpt

REPLACE - 图3

TableName

REPLACE - 图4

PartitionNameListOpt

REPLACE - 图5

InsertValues

REPLACE - 图6

  1. ReplaceIntoStmt ::=
  2.     'REPLACE' PriorityOpt IntoOpt TableName PartitionNameListOpt InsertValues
  4. PriorityOpt ::=
  5.     ( 'LOW_PRIORITY' | 'HIGH_PRIORITY' | 'DELAYED' )?
  7. IntoOpt ::= 'INTO'?
  9. TableName ::=
  10.     Identifier ( '.' Identifier )?
  12. PartitionNameListOpt ::=
  13.     ( 'PARTITION' '(' Identifier ( ',' Identifier )* ')' )?
  15. InsertValues ::=
  16.     '(' ( ColumnNameListOpt ')' ( ValueSym ValuesList | SelectStmt | '(' SelectStmt ')' | UnionStmt ) | SelectStmt ')' )
  17. |   ValueSym ValuesList
  18. |   SelectStmt
  19. |   UnionStmt
  20. |   'SET' ColumnSetValue? ( ',' ColumnSetValue )*

Examples

  1. mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
  2. Query OK, 0 rows affected (0.12 sec)
  4. mysql> INSERT INTO t1 (c1) VALUES (1), (2), (3);
  5. Query OK, 3 rows affected (0.02 sec)
  6. Records: 3  Duplicates: 0  Warnings: 0
  8. mysql> SELECT * FROM t1;
  9. +----+----+
  10. | id | c1 |
  11. +----+----+
  12. |  1 |  1 |
  13. |  2 |  2 |
  14. |  3 |  3 |
  15. +----+----+
  16. 3 rows in set (0.00 sec)
  18. mysql> REPLACE INTO t1 (id, c1) VALUES(3, 99);
  19. Query OK, 2 rows affected (0.01 sec)
  21. mysql> SELECT * FROM t1;
  22. +----+----+
  23. | id | c1 |
  24. +----+----+
  25. |  1 |  1 |
  26. |  2 |  2 |
  27. |  3 | 99 |
  28. +----+----+
  29. 3 rows in set (0.00 sec)

MySQL compatibility

The REPLACE statement in TiDB is fully compatible with MySQL. If you find any compatibility differences, report a bug.

See also