MYSQL - Insert into table if not exists with no obvious primary/unique key

Question

I have a table with 2 columns, they are 'user' and 'action' and are used to denote when a user has completed a certain action. An entry should only be made if a user has not made the given action before so I feel something like the following is what is required:

IF NOT EXISTS (select * from actiontable WHERE user=1 and action=5) THEN 
INSERT INTO actiontable VALUES(1,5);

Yet this seems to be incorrect syntax and I can't find how I should go about this. Is this possible? Or is there some way to use a primary key or unique key to achieve this?

There is, I tried search for the code and the text separately but to no avail: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS(SELECT * FROM actiontable WHERE user=1 AND action=5 at line 1 — Smithy
– Smithy, Commented Feb 25, 2014 at 20:50
@Sam . . . You can only use if in the context of a stored procedure, function, or trigger. — Gordon Linoff
– Gordon Linoff, Commented Feb 25, 2014 at 20:58

dkasipovic · Accepted Answer · 2014-02-25 20:47:05Z

2

You could create a primary key on user,action and then try

INSERT INTO actiontable (`user`,`action`) VALUES (1,5);

It will fail if the entry already exists. If you do not want to raise an error, you could try

INSERT IGNORE INTO actiontable (`user`,`action`) VALUES (1,5);

answered Feb 25, 2014 at 20:47

dkasipovic

6,1301 gold badge23 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL - Insert into table if not exists with no obvious primary/unique key

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related