How to use IF in SQL

Question

I am trying to understand how to use if in SQL (in mySql).

I wrote this source code:

$query = "
IF NOT EXISTS(SELECT * FROM sec WHERE CHROM='1' AND POS='2')
    INSERT INTO sec (CHROM, POS) VALUES ('1','2')
END IF
";
mysqli_query($config, $query) or die('Eror: ' . mysqli_error($config));

and I got this error:

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 'IF NOT EXISTS(SELECT * FROM sec WHERE CHROM='1' AND POS='2') INSERT INTO se' at line 1

How should I correct the error?

possible duplicate of [How to 'insert if not exists' in MySQL?](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — Aziz Shaikh, Dec 22 '14 at 07:16

score 1 · Answer 1 · answered Dec 22 '14 at 07:22

1

There is no need for IF here. You can simply do:

INSERT IGNORE INTO sec (CHROM, POS) VALUES ('1','2')

The IGNORE keyword will tell mySql to treat duplicate key errors as mere warnings.

answered Dec 22 '14 at 07:22

markus

1,651
1
16
30

score 1 · Accepted Answer · answered Dec 22 '14 at 07:33

1

To answer your question if you really want to use an IF-statement:

IF NOT EXISTS(SELECT * FROM sec WHERE CHROM='1' AND POS='2')
BEGIN
    INSERT INTO sec (CHROM, POS) VALUES ('1','2')
END

answered Dec 22 '14 at 07:33

idstam

2,717
1
19
30

score 0 · Answer 3 · edited May 23 '17 at 12:12

0

there are 2 better choice for the answer except IF statement:

-> ON DUPLICATE KEY UPDATE (if you have another value to be inserted)

-> INSERT IGNORE (if only you want to check duplicate or not)

You can check for the detail here:

"INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

edited May 23 '17 at 12:12

Community

1
1

answered Dec 22 '14 at 09:17

wasis

39
1
2
8

How to use IF in SQL

3 Answers3