This topic describes how to use the REPLACE INTO
statement.
Syntax
You can execute the
REPLACE INTO
statement to insert data to a table by overwriting the existing data in real time.
When you execute this statement to insert data, the system determines whether the
data to be inserted already exists in the table by using the primary key and then
inserts the data accordingly.
- If the data to be inserted already exists, the system deletes this row of data and then inserts the data.
- If the data to be inserted does not exist, the system directly inserts the data.
Note The primary key mentioned in this topic can be the primary key of a single column
or the composite primary key of multiple columns.
Syntax:
REPLACE INTO table_name [(column_name,...)] VALUES ({Constant|NULL|DEFAULT},...),(...),...
Examples
- Insert a single record to the customer table by specifying the column names in the
REPLACE INTO
statement.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 records to the customer table without specifying the column names.
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');