REPLACE
The REPLACE
statement is semantically a combined DELETE
+INSERT
statement. It can be used to simplify application code.
Synopsis
- ReplaceIntoStmt
- PriorityOpt
- IntoOpt
- TableName
- PartitionNameListOpt
- InsertValues
ReplaceIntoStmt ::=
'REPLACE' PriorityOpt IntoOpt TableName PartitionNameListOpt InsertValues
PriorityOpt ::=
( 'LOW_PRIORITY' | 'HIGH_PRIORITY' | 'DELAYED' )?
IntoOpt ::= 'INTO'?
TableName ::=
Identifier ( '.' Identifier )?
PartitionNameListOpt ::=
( 'PARTITION' '(' Identifier ( ',' Identifier )* ')' )?
InsertValues ::=
'(' ( ColumnNameListOpt ')' ( ValueSym ValuesList | SelectStmt | '(' SelectStmt ')' | UnionStmt ) | SelectStmt ')' )
| ValueSym ValuesList
| SelectStmt
| UnionStmt
| 'SET' ColumnSetValue? ( ',' ColumnSetValue )*
Examples
mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
Query OK, 0 rows affected (0.12 sec)
mysql> INSERT INTO t1 (c1) VALUES (1), (2), (3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t1;
+----+----+
| id | c1 |
+----+----+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+----+
3 rows in set (0.00 sec)
mysql> REPLACE INTO t1 (id, c1) VALUES(3, 99);
Query OK, 2 rows affected (0.01 sec)
mysql> SELECT * FROM t1;
+----+----+
| id | c1 |
+----+----+
| 1 | 1 |
| 2 | 2 |
| 3 | 99 |
+----+----+
3 rows in set (0.00 sec)
MySQL compatibility
This statement is understood to be fully compatible with MySQL. Any compatibility differences should be reported via an issue on GitHub.
See also
Was this page helpful?