SQL Query Error - If Else Statement

Question

The following SQl query gives me the error:

IF EXISTS (SELECT * FROM comments WHERE user_id='2' AND course_id='1') 
UPDATE comments SET page1='exists' WHERE user_id='2' AND course_id='1' 
ELSE 
INSERT INTO comments (user_id,course_id,page1) VALUES ('2','1','inserted')

" #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 EXISTS (SELECT * FROM comments WHERE user_id='2' AND course_id='1') UPDATE co' at line 1 "

I'm sure the syntax is correct?!

Are you trying to do this as a standalone piece of SQL (in which case having a unique key on user_id and course_id , then using INSERT....ON DUPLICATE KEY UPDATE would be easier), or as lines within a stored procedure? — Kickstart
– Kickstart, Commented Jul 9, 2014 at 10:14
Then that is not valid SQL. IF is a flow control statement, and with a raw SQL query there is no flow to control. — Kickstart
– Kickstart, Commented Jul 9, 2014 at 10:21

Kickstart · Accepted Answer · 2014-07-09 10:23:14Z

2

Add a unique key covering user_id and course_id

Then just use

INSERT INTO comments (user_id,course_id,page1) 
VALUES ('2','1','inserted')
ON DUPLICATE KEY UPDATE page1='exists'

answered Jul 9, 2014 at 10:23

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3402227 Over a year ago

Thanks Kickstart that works perfect in phpMyAdmin. Although using this SQL statement in php doesn't seem to work. Inserting works but i assume its the duplicate key part that's causing the problems. Is there anyway around this? Sorry i thought if it works in phpMyAdmin it would also work in php.

Saharsh Shah · Accepted Answer · 2014-07-09 10:40:38Z

1

Check this IF-statement syntax:

Try this:

IF EXISTS (SELECT * FROM comments WHERE user_id='2' AND course_id='1') THEN 
    UPDATE comments SET page1='exists' WHERE user_id='2' AND course_id='1';
ELSE 
    INSERT INTO comments (user_id,course_id,page1) VALUES ('2','1','inserted');

OR

As per me you have to make practice of BEGIN...END block as shown below.

IF EXISTS (SELECT * FROM comments WHERE user_id='2' AND course_id='1') THEN 
BEGIN 
    UPDATE comments SET page1='exists' WHERE user_id='2' AND course_id='1';
END 
ELSE 
BEGIN 
    INSERT INTO comments (user_id,course_id,page1) VALUES ('2','1','inserted');
END

My above queries will work in Procedures(PL-SQL). If you want to use above query in SQL then use below code:

Create a UNIQUE constraint on your user_id & course_id columns, if one does not already exist:

ALTER TABLE comments ADD UNIQUE (user_id,course_id);

Use INSERT ... ON DUPLICATE KEY UPDATE:

INSERT INTO comments (user_id,course_id,page1) 
VALUES ('2','1','inserted') 
ON DUPLICATE KEY UPDATE page1='exists'

edited Jul 9, 2014 at 10:40

answered Jul 9, 2014 at 10:07

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

3 Comments

user330315 Over a year ago

I don't think that is valid MySQL syntax

user330315 Over a year ago

Did you try it? There is no IF statement in SQL (and neither in MySQL's SQL dialect)

user3402227 Over a year ago

Thanks for you efforts Saharsh however this still doesn't work

Collectives™ on Stack Overflow

SQL Query Error - If Else Statement

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related