Conditional mySQL statement. If true UPDATE, if false INSERT [duplicate]

This question already has answers here: MySQL 'UPDATE ON DUPLICATE KEY' without a unique column? (3 answers) Closed 10 months ago.

I'm trying to create more robust MySQL Queries and learn in the process. Currently I'm having a hard time trying to grasp the ON DUPLICATE KEY syntax and possible uses.

I have an INSERT Query 开发者_StackOverflow中文版that I want to INSERT only if there is no record with the same ID and name, otherwise UPDATE. ID and name are not UNIQUE but ID is indexed.ID isn't UNIQUE because it references another record from another table and I want to have multiple records in this table that reference that one specific record from the other table.

How can I use ON DUPLICATE KEY to INSERT only if there is no record with that ID and name already set else UPDATE that record?

I can easily achieve this with a couple of QUERIES and then have PHP do the IF ELSE part, but I want to know how to LIMIT the amount of QUERIES I send to MySQL.

---

UPDATE: Note you need to use IF EXISTS instead of IS NULL as indicated in the original answer.

Code to create stored procedure to encapsulate all logic and check if Flavours exist:

DELIMITER //

DROP PROCEDURE `GetFlavour`//
CREATE PROCEDURE `GetFlavour`(`FlavourID` INT, `FlavourName` VARCHAR(20))
BEGIN
IF EXISTS (SELECT * FROM Flavours WHERE ID = FlavourID) THEN
UPDATE Flavours SET ID = FlavourID;
ELSE
INSERT INTO Flavours (ID, Name) VALUES (FlavourID, FlavourName);
END IF;
END //

DELIMITER ;

ORIGINAL:

You could use this code. It will check for the existence of a particular record, and if the recordset is NULL, then it will go through and insert the new record for you.

IF (SELECT * FROM `TableName` WHERE `ID` = 2342 AND `Name` = 'abc') IS NULL THEN
INSERT INTO `TableName` (`ID`, `Name`) VALUES ('2342', 'abc');
ELSE UPDATE `TableName` SET `Name` = 'xyz' WHERE `ID` = '2342';
END IF;

I'm a little rusty on my MySQL syntax, but that code should at least get you most of the way there, rather than using ON DUPLICATE KEY.

---

id and name are not unique but id is indexed. id isn't unique

How can I use ON DUPLICATE KEY to INSERT only if there is no record with that id and name already set else UPDATE that record?

You can't. ON DUPLICATE KEY UPDATE needs a unique or primary key to determine which row to update. You are better off having PHP do the IF ELSE part.

edit:

If the combination of name and id IS supposed to be unique, you can create a multi-column UNIQUE index. From there you can use ON DUPLICATE KEY UPDATE.

---

Why not just use a stored procedure, then you can embed all the logic there are plus you have a reusable piece of code (e.g. the stored proc) that you can use in other applications. Finally, this only requires one round trip to the server to call the stored proc.