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?

Answer 1

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"

Answer 2

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

Answer 3

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.