php - Conditional mySQL statement. If true UPDATE, if false INSERT

Question

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 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.

Answer 1

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.