Query error INSERT if not exist

Question

Hi this might be an easy question. but after trying multiple things i gave up.

Cant see what is wrong with this query:

IF NOT EXISTS(SELECT email FROM gebr_tickets WHERE email ='test@test.nl' ) 
INSERT INTO gebr_tickets (name,gebr_name, password, email, RelatieID) 
VALUES ('k','k','cmmNkftA','test@test.nl',170)

the error is :

[Err] 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 email FROM gebr_tickets WHERE email ='test@test.nl' ) INSERT INTO'

http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table this link maybe help you to solve your problem. — Rakibul Hasan, Apr 30 '15 at 22:18

score 1 · Answer 1 · answered Jan 09 '15 at 10:35

1

You can try the below code for ignoring duplicate records

INSERT IGNORE INTO gebr_tickets (name,gebr_name, password, email, RelatieID) 
VALUES ('k','k','cmmNkftA','test@test.nl',170);

answered Jan 09 '15 at 10:35

Bhavya Shaktawat

2,504
1
13
11

it will still insert it – Flidiot Jan 09 '15 at 10:51

score 0 · Answer 2 · answered Jan 09 '15 at 10:35

0

Simplest approach would be

INSERT INTO gebr_tickets (NAME,gebr_name, PASSWORD, email, RelatieID) 
SELECT 'k','k','cmmNkftA','test@test.nl',170 FROM DUAL 
WHERE NOT EXISTS (SELECT email FROM gebr_tickets WHERE email ='test@test.nl' )

This is another way .

DELIMITER $$
CREATE PROCEDURE `sp_insert` ()
BEGIN
IF NOT EXISTS (SELECT email FROM gebr_tickets WHERE email ='test@test.nl' )
THEN


INSERT INTO gebr_tickets (name,gebr_name, password, email, RelatieID) 
VALUES ('k','k','cmmNkftA','test@test.nl',170);


END IF;

END$$
DELIMITER ; 

CALL sp_insert(); 

DROP PROCEDURE IF EXISTS `sp_insert`;

answered Jan 09 '15 at 10:35

Akhil

2,602
23
36

this will insert multiple times? – Flidiot Jan 09 '15 at 10:42
No. Only once. Try this and see – Akhil Jan 09 '15 at 10:44
yeah in navicat it inserts multiple times XD – Flidiot Jan 09 '15 at 10:49
Tried the first approach? – Akhil Jan 09 '15 at 10:51
yes its in a sprintf condition so that would be the best way – Flidiot Jan 09 '15 at 10:52

score 0 · Answer 3 · answered Jan 09 '15 at 10:35

0

You can't use control statements like IF outside of procedures/functions/triggers. You need a shell around that.

answered Jan 09 '15 at 10:35

juergen d

201,996
37
293
362

Query error INSERT if not exist

3 Answers3