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


Tip of the Day
Language: SQL
Expertise: Intermediate
Apr 15, 2019

WEBINAR:

On-Demand

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


Using the Insert on Duplicate Key Update in MySQL

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');
M S Sridhar
 
Thanks for your registration, follow us on our social networks to keep up-to-date