INSERT INTO … ON DUPLICATE KEY UPDATE

CREATE TABLE devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uniqueID int NOT NULL UNIQUE,
    firstName VARCHAR(100),
    secondName VARCHAR(100)
);

INSERT INTO devices (firstName, secondName)
VALUES (‘James’,’Froggatt’);

… now

INSERT INTO devices (firstName, secondName)
VALUES (‘James2′,’Froggatt2’);

INSERT INTO devices ( firstName, secondName) VALUES(“Harry”,”Burns”) ON DUPLICATE KEY UPDATE
firstName=”Emma”, secondName=”Poppy”;

INSERT INTO devices ( firstName, secondName) VALUES(“Harry”,”Burns”) ON DUPLICATE KEY UPDATE
firstName=”Jacky”, secondName=”Chan”;


Start again without the unique key

CREATE TABLE devices (
id INT AUTO_INCREMENT PRIMARY KEY,
firstName VARCHAR(100),
secondName VARCHAR(100)
);

INSERT INTO devices ( firstName, secondName) VALUES(“Harry”,”Burns”) ON DUPLICATE KEY UPDATE
id=10;

INSERT INTO devices ( firstName, secondName) VALUES(“James”,”Froggatt”) ON DUPLICATE KEY UPDATE
id=8;


INSERT INTO devices ( firstName, secondName) VALUES(“Danny”,”Twatface”) ON DUPLICATE KEY UPDATE
id=2;

INSERT INTO devices ( firstName, secondName) VALUES(“James”,”Frodo”) ON DUPLICATE KEY UPDATE
id=2;

INSERT INTO devices ( firstName, secondName) VALUES(“Danny”,”Twatface”) ON DUPLICATE KEY UPDATE
firstName=”Donny”, secondName=”Furnisher”;

INSERT INTO devices (firstName, secondName) VALUES("James", "Froggatt") ON DUPLICATE KEY UPDATE    
firstName="Jaaammmeess", secondName="Frooooggatt";

INSERT INTO devices (id, firstName, secondName) VALUES(2, “James”, “Froggatt”) ON DUPLICATE KEY UPDATE
firstName=”Jaaammmeess”, secondName=”Frooooggatt”;

INSERT INTO devices (id) VALUES(5) ON DUPLICATE KEY UPDATE
firstName=”Dunny”, secondName=”Twatface2″;

INSERT INTO devices (id) VALUES(NULL) ON DUPLICATE KEY UPDATE
firstName=”Bernard”, secondName=”Ballface”;

INSERT INTO devices (id) VALUES(“”) ON DUPLICATE KEY UPDATE
firstName=”Bernard”, secondName=”Ballface”;

INSERT INTO devices (id) VALUES(888) ON DUPLICATE KEY UPDATE
firstName=”Bernard”, secondName=”Ballface”;

INSERT INTO devices (id, firstName, secondName) VALUES(8, “James”, “Froggatt”) ON DUPLICATE KEY UPDATE
firstName=”Robin”, secondName=”Gode”;

INSERT INTO devices (id, firstName, secondName) VALUES(NULL, “James”, “Froggatt”) ON DUPLICATE KEY UPDATE
id=889;

INSERT INTO devices (id, firstName, secondName) VALUES(NULL, “Jon”, “Tilby”) ON DUPLICATE KEY UPDATE
id=889;

INSERT INTO devices (id, firstName, secondName) VALUES(NULL, “Brian”, “Twatter”) ON DUPLICATE KEY UPDATE
id=891;

INSERT INTO devices (id, firstName, secondName) VALUES(891, “Brian2”, “Twatter2”) ON DUPLICATE KEY UPDATE
id=891;


INSERT INTO devices (id, firstName, secondName) VALUES(8, “Robin”, “Gode”) ON DUPLICATE KEY UPDATE
firstName=”Bobin”, secondName=”Foody”;

INSERT INTO devices (id, firstName, secondName) VALUES(8, “Robin2”, “Gode2”) ON DUPLICATE KEY UPDATE
firstName=”Bobin”;

INSERT INTO devices (id) VALUES(8) ON DUPLICATE KEY UPDATE
firstName=”Bobin2″, secondName=”Foody2″;

INSERT INTO devices (id) VALUES(10000) ON DUPLICATE KEY UPDATE
firstName=”Bobin3″, secondName=”Foody3″;

INSERT INTO devices (id) VALUES(id) ON DUPLICATE KEY UPDATE
firstName=”Bobin4″, secondName=”Foody4″;

INSERT INTO devices (firstName,secondName)
VALUES (“Harry”,”Burns”)
ON DUPLICATE KEY UPDATE
firstName = “Barry”,
secondName = “Hurns”;


If you specify an 

ON DUPLICATE KEY UPDATE
 clause and a row to be inserted would cause a duplicate value in a 
UNIQUE
 index or 
PRIMARY KEY
, an 
UPDATE
 of the old row occurs. For example, if column 
a
 is declared as 
UNIQUE
 and contains the value 
1
, the following two statements have similar effect:

dev.mysql.com

CREATE TABLE devices (
id INT AUTO_INCREMENT PRIMARY KEY,
uniqueID VARCHAR(100) UNIQUE,
firstName VARCHAR(100),
secondName VARCHAR(100)
);

INSERT INTO devices (uniqueID,firstName,secondName)
VALUES (“james88″,”Harry”,”Burns”)
ON DUPLICATE KEY UPDATE
uniqueID=”james88″,
firstName = “Harry “,
secondName = “Froggatt”;

INSERT INTO devices (uniqueID,firstName,secondName)
VALUES (“james88″,”Harry”,”Burns”)
ON DUPLICATE KEY UPDATE
uniqueID=”james88″,
firstName = “HarryChange”,
secondName = “FroggattChange”;

INSERT INTO devices (uniqueID,firstName,secondName)
VALUES (“james88″,”Harry”,”Burns”)
ON DUPLICATE KEY UPDATE
uniqueID=”james88″,
firstName = “HarryChange”,
secondName = “FroggattChangeAGAIN”;

INSERT INTO devices (uniqueID, secondName)
VALUES (“james88″,”Burns”)
ON DUPLICATE KEY UPDATE
uniqueID=”james88″,
secondName = “FroggattChangeAGAINAGAIN”;

INSERT INTO devices (uniqueID,firstName,secondName)
VALUES (“newUser1″,”Harry”,”Burns”)
ON DUPLICATE KEY UPDATE
firstName = “Mike”,
secondName = “Trumpy”;

INSERT INTO devices (uniqueID,firstName,secondName)
VALUES (“newUser1″,”Harry”,”Burns”)
ON DUPLICATE KEY UPDATE
firstName = “Mike”,
secondName = “Trumpy”;


OK so practical example in terms of my project

CREATE TABLE accomodationTest (
id INT AUTO_INCREMENT PRIMARY KEY,
accomodationName VARCHAR(100),
country VARCHAR(100),
maxGuests INT (10));

INSERT INTO accomodationTest(accomodationName,country,maxGuests)
VALUES (“Banbury Mews”,”UK”,”10″)
ON DUPLICATE KEY UPDATE
id = “8”;


INSERT INTO accomodationTest(accomodationName,country,maxGuests)
VALUES (“Banbury Mews2″,”UK2″,”20”)
ON DUPLICATE KEY UPDATE
id = “”;

Leave a Reply