CODEWITHSUNDEEP
mysqlsql
Dr.Kameleon
Dr.Kameleon

Reputation: 22820

MySQL Procedure - Insert row if not exists

OK, this is what I want to do :

This is what I've managed so far (for the "if doesn't exist, create it" part) :

INSERT INTO `objects` (`id`,`name`)
    SELECT NULL,'someObj2' FROM `objects`
        WHERE NOT EXISTS
             (SELECT name FROM `objects` WHERE `name`='someObj2');
SELECT LAST_INSERT_ID();

How can I get the id (instead of LAST_INSERT_ID()) if the entry does exist?

P.S. Yep, I know that the main reason I can't get my head around SQL is the degree at which I'm used to the more classical if-then-else approach of regular programming languages... lol

UPDATE :

I keep trying and trying and this what I've managed so far (as a stored procedure) :

IF EXISTS (SELECT * FROM `objects` WHERE `name` = NAME) 
THEN
    SELECT `id` FROM `objects` WHERE `name` = NAME;
ELSE
    INSERT INTO `objects` (`id`,`name`) VALUES(NULL,NAME);
    SELECT LAST_INSERT_ID() AS 'id';
END IF

and calling it like: CALL insertObject("someObj2");

However, it's not working as expected - neither does it add the entry, nor does it return the id (instead it returns all ids in the table...). Any idea what could be going wrong?

Upvotes: 0

Views: 1589

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

It looks like you are trying to enforce a unique constraint on name. If so, you can also do this by just declaring the column to be unique or equivalently creating a unique index:

create unique index objects_name on objects(name);

If this is true, then change the question from getting the last inserted id to just getting the id for name:

select id
from objects o
where o.name = 'someObj2';

I hasten to add that in a high-transaction environment where things are being added and deleted quickly, any approach might have a problem. Consider your code, the row could be inserted and then deleted, even before the last_insert_id() is executed. If you are dealing with a high transaction environment with potential race conditions, then you need to use transactions and locking to do what you want.

Upvotes: 2

Related Questions