CODEWITHSUNDEEP
mysql
C. E.
C. E.

Reputation: 10607

Usage of MySQL's "IF EXISTS"

Here are two statements that I'd like to work, but which return error messages:

IF EXISTS (SELECT * FROM gdata_calendars WHERE `group` =  ? AND id = ?) SELECT 1 ELSE SELECT 0

and

IF ((SELECT COUNT(*) FROM gdata_calendars WHERE `group` =  ? AND id = ?) > 0)  SELECT 1 ELSE SELECT 0;

The question marks are there because I use parametrized, prepared, statements with PHP's PDO. However, I have also tried executing this with data manually, and it really does not work.

While I'd like to know why each of them doesn't work, I would prefer to use the first query if it can be made to work.

Upvotes: 84

Views: 430052

Answers (5)

Esten
Esten

Reputation: 559

If your table has an auto-incrementing primary key, you can use REPLACE INTO ... VALUES

SELECT @id := id FROM tableName WHERE fieldName='criteria value' LIMIT 1;
REPLACE INTO tableName(id, fieldName, col1, col2)
VALUES (@id, 'criteria value', 'value1', 'value2')

If the select statement returns NULL, then a new row is inserted. Otherwise, if a row is found, it will update the row with key @id.

Upvotes: 0

Dilraj Singh
Dilraj Singh

Reputation: 1011

SELECT IF((
     SELECT count(*) FROM gdata_calendars 
     WHERE `group` =  ? AND id = ?)
,1,0);

For Detail explanation you can visit here

Upvotes: -1

user6096790
user6096790

Reputation: 430

The accepted answer works well and one can also just use the 

If Exists (...) Then ... End If;

syntax in Mysql procedures (if acceptable for circumstance) and it will behave as desired/expected. Here's a link to a more thorough source/description: https://dba.stackexchange.com/questions/99120/if-exists-then-update-else-insert

One problem with the solution by @SnowyR is that it does not really behave like "If Exists" in that the (Select 1 = 1 ...) subquery could return more than one row in some circumstances and so it gives an error. I don't have permissions to respond to that answer directly so I thought I'd mention it here in case it saves someone else the trouble I experienced and so others might know that it is not an equivalent solution to MSSQLServer "if exists"!

Upvotes: 2

SnowyR
SnowyR

Reputation: 471

I found the example RichardTheKiwi quite informative.

Just to offer another approach if you're looking for something like IF EXISTS (SELECT 1 ..) THEN ...

-- what I might write in MSSQL

IF EXISTS (SELECT 1 FROM Table WHERE FieldValue='')
BEGIN
    SELECT TableID FROM Table WHERE FieldValue=''
END
ELSE
BEGIN
    INSERT INTO TABLE(FieldValue) VALUES('')
    SELECT SCOPE_IDENTITY() AS TableID
END

-- rewritten for MySQL

IF (SELECT 1 = 1 FROM Table WHERE FieldValue='') THEN
BEGIN
    SELECT TableID FROM Table WHERE FieldValue='';
END;
ELSE
BEGIN
    INSERT INTO Table (FieldValue) VALUES('');
    SELECT LAST_INSERT_ID() AS TableID;
END;
END IF;

Upvotes: 35

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

You cannot use IF control block OUTSIDE of functions. So that affects both of your queries.

Turn the EXISTS clause into a subquery instead within an IF function

SELECT IF( EXISTS(
             SELECT *
             FROM gdata_calendars
             WHERE `group` =  ? AND id = ?), 1, 0)

In fact, booleans are returned as 1 or 0

SELECT EXISTS(
         SELECT *
         FROM gdata_calendars
         WHERE `group` =  ? AND id = ?)

Upvotes: 148

Related Questions