sql if exists simple syntax error

Question

I keep getting this syntax error but can't find anything wrong with it when comparing to other examples.

if EXISTS (select 1 from City where name = 'Perth')
THEN  Print 'Record exits - Update'
ELSE  Print 'Record doesn''t exist - Insert'

I find error:

#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
1 from City where name = 'Perth') THEN Print 'Record e' at line 1

I get this both on zend cloud and normal phpmyadmin mysql 5

RocketDonkey · Accepted Answer · 2012-11-17 21:10:28Z

That isn't actually a valid MySQL query. It looks like you are trying to mix together SQL with how you want to display output based on the whether the query exists or not. You can use this to return whether or not Perth exists in SQL:

SELECT EXISTS(SELECT 1 FROM City WHERE name = 'Perth')

This will return 1 or 0, which you can then parse with your server-side scripts. The reason it is giving you a syntax error is because MySQL IF statements take the form IF(condition, <action if true>, <action if false>), with no use of THEN or ELSE (as is common in programming languages). Also, MySQL doesn't have an explicit PRINT statement, but you could use SELECT to somewhat accomplish what you want above (note that we can remove EXISTS as False will be implied if the result returns nothing):

SELECT IF(
      (SELECT 1 FROM City WHERE name = 'Perth'),
      (SELECT 'Record exists - update'),
      (SELECT 'Record does not exist - Insert')
)

Sami · Accepted Answer · 2012-11-19 10:05:25Z

1

You need to use 'select' instead of print in following way

select IF((select 1 from city where name='Perth'),
'Record exits - Update','Record does not exist - Insert');

SQL Fiddle Demo. Following shows the use of IF in select Statement

IF((select 1 from city where name='Perth'),
'Record exits - Update','Record does not exist - Insert');

IF contains two messages.

First : 'Record exits - Update' Second : 'Record does not exist - Insert'

First message is printed if (select 1 from city where name='Perth') has some results(equivalent to EXISTS) otherwise you will get second message

edited Nov 19, 2012 at 10:05

answered Nov 17, 2012 at 22:26

Sami

8,4599 gold badges72 silver badges104 bronze badges

2 Comments

Thomas Over a year ago

hey just wondering what setup your using as this doesn't work for me even if I change the print statements to select ones

Sami Over a year ago

@Tommy. You are right. My previously told way of using if was for stored procedures. You can see my edited answer. hope it explains use of if in select statement.

Glenn · Accepted Answer · 2012-11-16 03:53:56Z

An alternative way: using a grouping function will always return a record. If there are no records to operate on, the result of the group by function will be NULL. You could use that as a decision mechanism.

postgres=# create table city(name text);
CREATE TABLE
postgres=#
postgres=# select COALESCE( max('Record exists - Update'), 'Record doesn''t exist - Insert' ) as state
postgres-#   from city
postgres-#   where name = 'Perth';
             state
-------------------------------
 Record doesn't exist - Insert
(1 row)


postgres=#
postgres=# insert into city values('Perth');
INSERT 0 1
postgres=#
postgres=# select COALESCE( max('Record exists - Update'), 'Record doesn''t exist - Insert' )  as state
postgres-#   from city
postgres-#   where name = 'Perth';
         state
------------------------
 Record exists - Update
(1 row)

Collectives™ on Stack Overflow

sql if exists simple syntax error

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related