MySQL update on duplicate example

The mysql query below inserts a new record in the employee table with id=12345, name=”John” and email=”hohn@example.com”. If the employee with this id already exist, it will update the name and the email instead of insert a new employee.

INSERT INTO employee(id,name,email)
VALUES(12345,"John","john@example.com") 
ON DUPLICATE KEY
    UPDATE name="John", email="john@example";

This query does the same, but the on duplicate key update clause references the values supplied from the insert clause instead of supply the value directly.

INSERT INTO employee(id,name,email)
VALUES(12345,"John","john@example.com") 
ON DUPLICATE KEY
    UPDATE name=VALUES(name), email=VALUES(email);

This query also does the same. It a MySQL replace statement, the syntax is exactly like the plain insert statement. It will do the insert if the old row doesn’t exist for the primary key or unique index, else it will delete the row and insert the new one.

REPLACE INTO employee(id,name,email)
VALUES(12345,"John","john@example.com");

Search within Codexpedia

Custom Search

Search the entire web

Custom Search