You can execute the REPLACE SELECT FROM statement to copy records from one table to another in real time by overwriting existing data. 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,...)]
query;

Parameters

  • query: the SELECT FROM TABLE or SELECT FROM VIEW statement.
  • column_name: the name of the column. If you only want to copy data from certain columns of the source table to the target table, the columns specified in the SELECT clause must have the same sequence and data types as those specified in the REPLACE clause.

Precautions

The target table must exist before you can execute the REPLACE SELECT FROM statement to insert the records.

Example

Copy data from only the specified columns of the customer table to the new_customer table by specifying the column names. 

REPLACE INTO new_customer (customer_id, customer_name, phone_num)
  SELECT customer_id, customer_name, phone_num FROM customer
  WHERE customer.customer_name = 'Alan';