REPLACE SELECT FROM copies records from one table (or view) to another in real time. For each record, it checks whether the primary key already exists in the target table. If it does, the existing record is deleted and the new one is inserted. If it doesn't, the record is inserted directly.
How it works
For each record being copied, AnalyticDB for MySQL runs the following steps:
Attempt to insert the record into the target table.
If a primary key conflict occurs, delete the conflicting record.
Insert the new record.
When to use REPLACE SELECT FROM
Use REPLACE SELECT FROM when you need to overwrite existing records based on primary key matches — for example, refreshing a summary table with updated source data.
Syntax
REPLACE INTO table_name
[(column_name, ...)]
query;Parameters
| Parameter | Description |
|---|---|
table_name | The name of the target table. The table must exist before you run the statement. |
column_name | (Optional) The columns to copy data into. If specified, the columns in the SELECT clause must match the listed columns in the same order and with compatible data types. |
query | A SELECT FROM TABLE or SELECT FROM VIEW statement that defines the records to copy. |
Prerequisites
The target table exists.
Example
Copy three columns from the customer table to the new_customer table, overwriting any existing record with a matching primary key.
REPLACE INTO new_customer (customer_id, customer_name, phone_num)
SELECT customer_id, customer_name, phone_num FROM customer
WHERE customer.customer_name = 'Alan';