REPLACE INTO is semantically a combined DELETE + INSERT operation: it overwrites an existing row when the primary key matches, or inserts a new row when no match is found.
Syntax
REPLACE INTO table_name [(column_name,...)] VALUES ({Constant|NULL|DEFAULT},...),(...),...Parameters
| Parameter | Description |
|---|---|
table_name | The target table. |
column_name | (Optional) The columns to populate. Omit to insert values for all columns in their defined order. |
Constant|NULL|DEFAULT | The value for each column: a literal constant, NULL, or the column's DEFAULT value. |
Note
The primary key can be a single-column primary key or a composite primary key spanning multiple columns.
How it works
When you execute REPLACE INTO, the system checks whether a row with the same primary key already exists:
Row exists — the system deletes the existing row and inserts the new row.
Row does not exist — the system inserts the row directly.
Examples
Insert a single row with column names
REPLACE INTO customer(customer_id,customer_name,phone_num,city_name,sex,id_number,home_address,office_address,age,login_time)
VALUES
(002367,'Bob','13900001234','Hangzhou',0,'987300','WestLake','CloudTown',23,'2018-03-02 10:00:00');Insert multiple rows without column names
Omit the column list when providing values for every column in their defined order.
REPLACE INTO customer VALUES
(002367,'John','13900001111','Hangzhou',0,'987300','WestLake','CloudTown',23,'2018-03-02 10:00:00'),
(002368,'Adam','13900002222','Hangzhou',0,'987300','WestLake','CloudTown',28,'2018-08-01 11:00:00'),
(002369,'Brook','13900003333','Hangzhou',1,'987300','WestLake','CloudTown',35,'2018-09-12 08:11:00');