/    /  MySQL –  Insert On Duplicate Key Update

MySQL –  Insert On Duplicate Key Update

 

In MySQL, the Insert on Duplicate Key Update statement extends the INSERT statement. A row that would cause a duplicate error value in a UNIQUE or PRIMARY KEY index column will be updated when we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement.

In other words, if we insert new values into the table and it causes a duplicate row in a UNIQUE OR PRIMARY KEY column, we will receive an error message. On the other hand, if we use the ON DUPLICATE KEY UPDATE clause in a SQL statement, it will update the old row with the new row values, regardless of whether the row has a unique or primary key column.

Example

The first step is to create a table named “employee” using the following statement:

CREATE TABLE employee (
emp_ID int AUTO_INCREMENT PRIMARY KEY,
Name varchar(45) DEFAULT NULL,
Email varchar(45) DEFAULT NULL,
County varchar(25) DEFAULT NULL
);

The next step is to insert the data into the table. The following statement should be executed:

INSERT INTO employee(emp_ID, Name, Email, County)
VALUES (1,'venu', 'venu@gmail.com', 'India'),
(2, 'madhu', 'madhu@gmail.com', 'US'),
(3, 'jhon', 'jhon@gmail.com', 'UK');

Verify the insert operation by executing the SELECT statement:

SELECT * from employee;

Following is the output where we have three rows in the table:

m

Finally, we will add a row with a duplicate value in the emp_ID column:

INSERT INTO employee(emp_ID, Name, Email, County)
VALUES (3, 'John', 'john@gmail.com', 'UK')
ON DUPLICATE KEY UPDATE County = 'US';

Following successful execution of the above query.

In the below out, we can see that the row id=3 already exists. So the query only updates the country uK withUS.

SELECT * from employee;

m