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');