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