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
clause and a row to be inserted would cause a duplicate value in aON DUPLICATE KEY UPDATEindex orUNIQUE, anPRIMARY KEYof the old row occurs. For example, if columnUPDATEis declared asaand contains the valueUNIQUE, the following two statements have similar effect:dev.mysql.com1
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 = “”;