If record does not exist, insert record into mysql and preserve unique id in either scenario

Question

Here is my code. I am getting a syntax error for "IF NOT EXISTS..." statement. It is pretty self-explanatory on what I want to do here. If the record exists in the terms table, I want to know the unique term_id. If the record does not exist, I want to insert a new record and get the unique term_id which will be needed in the subsequent statement.

How to write this statement syntactically correct while preserving the logic. Thanks.

 INSERT INTO `koolkat_temp`.`creatives` (`creative_id`, `creative_title`, `creative_image_name`) VALUES (NULL, 'xyz', 'xyz');
 SET @record_pointer = LAST_INSERT_ID();
 IF NOT EXISTS (SELECT `term_id` INTO @term_id_placement FROM `terms` WHERE `name` LIKE 'xyz.com' and `taxonomy` LIKE 'placement') THEN
     INSERT INTO `terms` (`term_id` ,`name` ,`slug`, `taxonomy`, `description` ,`parent`, `count`) VALUES (NULL, 'xyz.com', 'xyz.com',  'placement', '', 0, 0);
     SET @term_id_placement = LAST_INSERT_ID();
 ENDIF; 
 INSERT IGNORE INTO `term_relationships` (`creative_id` ,`term_id` ,`term_order`) VALUES (@record_pointer, @term_id_placement, 0);

I think this might be similar to what you're looking for: [link](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — SteveK, Apr 08 '15 at 04:59
while this will work, I am still not sure on how to extract the unique term_id in case if the record exists. — hvs, Apr 08 '15 at 05:06
You can run two queries, this simplifies and makes the code easier to maintain. — Cristik, Apr 08 '15 at 05:14

Cristik · Accepted Answer · 2015-04-08T10:59:05.277

You can break your creatives query into three simpler ones (search for term id, insert if term doesn't exist, insert into creatives), and wrap them, and the term_relationships one inside a transaction, this will have the benefits of keeping the atomicity of the whole operation, and will also simplify the query, thus being easier to understand and maintain.

Something along the lines (i'm using pseudocode as I don't know what platform are you using to connect to mysql):

execQuery("BEGIN"); //begin the transaction
termId = execQuery("SELECT `term_id`...")->firstRecord;
if not termId then
    execQuery("INSERT INTO terms ...");
    termId = mysqlInsertId();
endif
execQuery("INSERT INTO `koolkat_temp`.`creatives` ...");
execQuery("INSERT IGNORE INTO `term_relationships` ...");
execQuery("COMMIT"); // commit the transaction to make the queries effects permanent

If you want to keep it server side, you can create a stored procedure that contains the above queries. You can configure the stored procedure to allow parameters, thus allowing easy customisation of your queries. Something along the lines (pardon any syntax errors, I don't have a mysql in front of me right now):

-- change the default ';' delimiter to be able to use it inside the SP
DELIMITER $$
CREATE PROCEDURE add_term_and_other(term varchar(50))
BEGIN
    BEGIN;
    INSERT INTO `koolkat_temp`.`creatives` ...;
    SET @record_pointer = LAST_INSERT_ID();
    SELECT `term_id` INTO @term_id_placement FROM ..;
    IF ISNULL(@term_id_placement) THEN
        INSERT INTO `terms` ...;
        SET @term_id_placement = LAST_INSERT_ID();
    ENDIF;
    INSERT IGNORE INTO `term_relationships` ...;
    COMMIT;
END$$
-- get back to the original delimiter
DELIMITER ;

If record does not exist, insert record into mysql and preserve unique id in either scenario

1 Answers1