CODEWITHSUNDEEP
mysql
Remy B
Remy B

Reputation: 61

PHP SQL IF NOT EXISTS won't work

I've got a question about SQL (php), I want to INSERT data in my table. But I want to use the IF NOT EXIST value. What I've tried:

INSERT INTO vrienden (id, userid, vriendmetid, accepted) VALUES (null, '1', '20', '0') WHERE NOT EXISTS (SELECT * FROM vrienden WHERE userid='1' AND vriendmetid='20')

I'm not sure what's wrong, because I get the following error:

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 'WHERE NOT EXISTS (SELECT * FROM vrienden WHERE userid='1' AND vriendmetid='20')' at line 1

Thanks.

Upvotes: 1

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You want insert . . . select, not insert . . . values:

INSERT INTO vrienden (id, userid, vriendmetid, accepted) 
     SELECT x.*
     FROM (select null as id, '1' as userid, '20' as vriendmetid, '0' as accepted) x
     WHERE NOT EXISTS (SELECT 1 FROM vrienden v WHERE v.userid = x.userid AND v.vriendmetid = x.vriendmetid);

However, you probably shouldn't be doing this in the INSERT. Instead, create a unique index/constraint:

create unique index unq_vrienden_userid_vriendmetid on vrienden(userid, vriendmetid);

This way, the database will ensure uniqueness of the columns, so your application does not have to.

Upvotes: 2

Related Questions