You can execute the REPLACE INTO statement to insert data to a table by overwriting the existing data in real time. The system first checks whether the primary key of a record to be inserted is the same as that of an existing record. If they are the same, the system will delete the existing record and insert the new record. Otherwise, the system will only insert the new record.

Syntax

REPLACE INTO table_name [(column_name,...)] VALUES ({Constant|NULL|DEFAULT},...),(...),...      

Example

  • Insert a 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,'Alan','13678973421','Hangzhou',0,'987300','West Lake','Cloud Town',23,'2018-03-02 10:00:00');                
  • Insert multiple records to the customer table without specifying the column names.
    REPLACE INTO 
    customer values
    (002367,'Tom','13678973421','Hangzhou',0,'987300','West Lake','Cloud Town',23,'2018-03-02 10:00:00'),(002368,'Alex,'13878971234','Hangzhou',0,'987300','West Lake','Cloud Town',28,'2018-08-01 11:00:00'),(002369,'Eric','13968075284','Hangzhou',1,'987300','West Lake','Cloud Town',35,'2018-09-12 08:11:00');