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','136****3421','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','136****3421','Hangzhou',0,'987300','WestLake','CloudTown',23,'2018-03-02 10:00:00'),(002368,'Adam','138****1234','Hangzhou',0,'987300','WestLake','CloudTown',28,'2018-08-01 11:00:00'),(002369,'Brook','139****5284','Hangzhou',1,'987300','WestLake','CloudTown',35,'2018-09-12 08:11:00');