dcsimg
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Tip: Using the Insert on Duplicate Key Update in MySQL

Learn more about the insert on duplicate key update and how to use it in MySQL.


advertisement

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


An interesting statement in MySQL is the INSERT ON DUPLICATE KEY UPDATE. This statement tries to INSERT a record, and on finding a duplicate key, performs the action provided alongside.

Example:

INSERT INTO CITIES(ID, CITY, COUNTRY, CONTINENT)
VALUES(10, 'Ottawa', 'Canada', 'North America')
ON DUPLICATE KEY UPDATE ID = VALUES(ID) + 1, CITY='Ottawa City';

In this case, the INSERT statement is attempting to insert a record with the ID as 10. Since this is already available as per our prerequisites, the condition ON DUPLICATE KEY is triggered and the ID and the CITY names are updated on the existing record. Please note, the change is made onto the existing record.

Prerequisites:

Create the following table and populate data as below.

CREATE TABLE 'CITIES' (
'ID' INT(11) NOT NULL AUTO_INCREMENT,
'CITY' VARCHAR(25) NOT NULL,
'COUNTRY' VARCHAR(25) NOT NULL,
'CONTINENT' VARCHAR(25) NOT NULL,
PRIMARY KEY ('ID')
);

Also, add records as below, using which we will query for all cities that belong to a particular continent.

INSERT INTO 'CITIES' ('CITY', 'COUNTRY', 'CONTINENT')
VALUES
('Athens', 'Greece', 'Europe'),
('New York', 'USA', 'North America'),
('Barcelona', 'Spain', 'Europe'),
('Tokyo', 'Japan', 'Asia'),
('Mumbai', 'India', 'Asia'),
('Sydney', 'Australia', 'Australia'),
('Cape Town', 'South Africa', 'Africa'),
('Buenos Aires', 'Argentina', 'South America'),
('Kabul', 'Afghanistan', 'Asia'),
('Ottawa', 'Canada', 'North America');

 

Visit the DevX Tip Bank

 





   
Sridhar M S. is a Java developer from Bangalore, India. He holds a master's degree in Computer Science.
Thanks for your registration, follow us on our social networks to keep up-to-date