In MySQL, sometimes, when you are writing an INSERT query to insert data in a table, you also want to update the data if it already exists. Well, you can do that by appending the ON DUPLICATE KEY UPDATE statement to your INSERT statement. Here are the examples.
-- Creat test data DROP TABLE IF EXISTS person; CREATE TABLE person(id INT PRIMARY KEY, name VARCHAR(50), age INT); INSERT INTO person(id, name, age) VALUES(1, 'john', 33); SELECT * FROM person; -- Update only name to 'John Smith'. INSERT INTO person (id, name, age) VALUES(1, "john smith", 19) ON DUPLICATE KEY UPDATE name="John Smith"; -- Update multiple columns. INSERT INTO person (id, name, age) VALUES(1, "john smith", 19) ON DUPLICATE KEY UPDATE name="John Smith", age=99; -- Use VALUES() to refer to the value you attempt to insert. INSERT INTO person (id, name, age) VALUES(1, "john smith", 19) ON DUPLICATE KEY UPDATE name=VALUES(name), age=VALUES(age); -- You can also use COALESCE() to return the first non-null value in a list. -- This ensure that you field is fill with data instead of null. INSERT INTO person (id, name, age) VALUES(1, "john smith", 19) ON DUPLICATE KEY UPDATE name=COALESCE(VALUES(name), name), age=COALESCE(VALUES(age), age);
Note: LAST_INSERT_ID() returns 0 if no insert nor update is performed.
About the author
Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.