CODEWITHSUNDEEP
javamysqlsql
Bernheart
Bernheart

Reputation: 637

Syntax SQL error on IF EXISTS statement

in my java project I need to check if a row exists in a table. In case of exist, I need to Update; if not, I need to create it. The Sql syntax to do this should be:

IF EXISTS(SELECT * FROM table1 WHERE column4='"+int4+"' AND column5='"+int5+"') "
                +"BEGIN "
+ "UPDATE table1"
+ "SET column1='"+int1+"', column2='"+int2+"' "
+ "WHERE column4='"+int4+"' and column5='"+int5+"' "
+ "END "
+ "ELSE"
+ "INSERT INTO table1 (column1, column2, column4, column5, column3) "
                + "VALUES ('" + int1 + "',"
                + "'" + int2 + "',"
                + "'" + int4 + "',"
                + "'" + int5 + "',"
                + "'" + int3 +"');

where int1, int2, int3, int4, int5 are integer values. Well, if I put this code I have an Sql syntax error on my java compiler : 

 com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 EXISTS(SELECT * FROM table1 WHERE column4='1' AND column5='0') BEGIN UPDATE' at line 1

But I can'y see the error

Upvotes: 0

Views: 295

Answers (1)

peterm
peterm

Reputation: 92785

You've got an error because in MySQL you can't use conditional statement IF other than in stored routines (stored procedure, stored function, trigger).

What you need is so called UPSERT which you can achieve in MySQL with INSERT INTO ... ON DUPLICATE KEY UPDATE. In order for it to work you have to have a UNIQUE INDEX on column4 and column5.

ALTER TABLE table1 ADD UNIQUE (column4, column5);

Now your INSERT statement might look like

INSERT INTO table1 (column1, column2, column4, column5, column3)
VALUES (?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE column1=VALUES(column1), column2=VALUES(column2);

Here is SQLFiddle demo

On a side note: Use parameterized queries instead of interpolating query strings. I'm not an expert in Java but I'm sure it has a first class infrastructure for that. Otherwise you are wide open to SQL-injections.

Upvotes: 3

Related Questions