MySQL – Replace
MySQL’s REPLACE statement is an extension of the SQL Standard. As with the INSERT statement, this command deletes the old row before adding the new record if the old row matches the new record in the table for a PRIMARY KEY or UNIQUE index.
The purpose of this statement is to update the existing records in the table in order to keep them current. If we use the standard insert query for this purpose, it will produce a duplicate entry for the PRIMARY KEY or a unique key error. To perform our task, we will use the REPLACE statement. One of two possible actions must be taken in response to the REPLACE command:
- The INSERT statement is executed if no matching value is found with the existing data row.
- The replace command will delete the existing row if the duplicate record is found, and then add the new record to the table.
REPLACE [INTO] table_name(column_list) VALUES(value_list);
With the help of an example, let us understand how the REPLACE statement in MySQL works. Using the following statement, we will create a table named “employee”:
CREATE TABLE employee ( ID int AUTO_INCREMENT PRIMARY KEY, Name varchar(45) DEFAULT NULL, Email varchar(45) DEFAULT NULL UNIQUE, County varchar(25) DEFAULT NULL );
As a next step, we need to insert the record into the table using the INSERT statement as follows:
INSERT INTO employee(ID, Name, Email, County) VALUES (1,'venu', 'email@example.com', 'India'), (2, 'madhu', 'firstname.lastname@example.org', 'US'), (3, 'jhon', 'email@example.com', 'UK');
Verify the records that can be shown in the output below by executing the SELECT statement:
SELECT * from employee;
Using the REPLACE statement, we can replace any old row with the new row after verifying the data in the table. Please execute the following statement to update the county of an employee whose ID is 3.
REPLACE INTO employee (id, County) VALUES(4,'US');
In order to verify that the replacement was successful, it is necessary to query the data of the table Person again after the above statement has been successfully executed.