MySQL REPLACE

The REPLACE statement in MySQL is an extension of the SQL Standard. This statement works the same as the INSERT statement, except that if an old row matches the new record in the table for a PRIMARY KEY or a UNIQUE index, this command deleted the old row before the new row is added.

This statement is required when we want to update the existing records into the table to keep them updated. If we use the standard insert query for this purpose, it will give a Duplicate entry for PRIMARY KEY or a UNIQUE key error. In this case, we will use the REPLACE statement to perform our task. The REPLACE command requires one of the two possible actions take place:

• If no matching value is found with the existing data row, then a standard INSERT statement is performed.
• If the duplicate record found, the replace command will delete the existing row and then adds the new record in the table.

In the REPLACE statement, the updation performed in two steps. First, it will delete the existing record, and then the newly updated record is added, similar to a standard INSERT command. Thus, we can say that the REPLACE statement performs two standard functions, DELETE and INSERT.

Syntax

The following are the syntax of REPLACE statement in MySQL:

REPLACE [INTO] table_name(column_list)  

VALUES(value_list);

MySQL REPLACE Example

Let us understand the working of the REPLACE statement in MySQL with the help of an example. First, we are going to create a table named “Person” using the following statement:

CREATE TABLE Person (  

  ID int AUTO_INCREMENT PRIMARY KEY,  

  Name varchar(45) DEFAULT NULL,  

  Email varchar(45) DEFAULT NULL UNIQUE,  

  City varchar(25) DEFAULT NULL  

);

Next, we need to fill the record into the table using the INSERT statement as below:

INSERT INTO Person(ID, Name, Email, City)   

VALUES (1,'Mike', '[email protected]', 'California'),   

(2, 'Alexandar', '[email protected]', 'New York'),   

(3, 'Adam', '[email protected]', 'Los Angeles'),  

(4, 'Peter', '[email protected]', 'Alaska');

Execute the SELECT statement to verify the records that can be shown in the below output:

After verifying the data into a table, we can replace any old row with the new row using the REPLACE statement. Execute the below statement that updates the city of a person whose id is 4.

REPLACE INTO Person (id, city)  

VALUES(4,'Amsterdam');

After the successful execution of the above statement, it is required to query the data of the table Person again to verify the replacement.

The value in the name and email columns are NULL now. It is because the REPLACE statement works as follows:

• This statement first tries to insert a new row into the Person table. But the insertion of a new row is failed because the id = 4 already exists in the table.
• So this statement first delete the row whose id = 4 and then insert a new row with the same id and city as Amsterdam. Since we have not specified the value for the name and email column, it was set to NULL.

MySQL REPLACE statement to update a row

We can use the following REPLACE statement to update a row data into a table:

REPLACE INTO Person  

SET ID = 1,  

    Name = 'Mike',  

    City = 'Birmingham';

The above syntax is similar to the UPDATE statement except for the REPLACE keyword. It is to note that we cannot use the WHERE clause with this statement.

Execute the below example that uses the REPLACE statement to update the city of the person named Mike from California to Birmingham.

REPLACE INTO Person  

SET ID = 1,  

    Name = 'Mike',  

    City = 'Birmingham';

After verification of the table, we can see the following output:

If we have not specified the column’s value in the SET clause, this command works like the UPDATE statement, which means the REPLACE statement will use the default value of that column.

MySQL REPLACE to insert data from the SELECT statement.

We can use the following REPLACE INTO statement to inserts data into a table with the data returns from a query.

REPLACE INTO table1(column_list)  

SELECT column_list  

FROM table2  

WHERE condition;

It is to note that the above REPLACE query is similar to the INSERT INTO SELECT statement. Execute the below example that uses the REPLACE INTO statement to copy a row within the same table.

REPLACE INTO Person(Name, City)  

SELECT Name, City   

FROM Person WHERE id = 2;

After verification of the table, we will get the following output. In this output, we can see that the copy of a row within the same table is successfully added.