Usage of MySQL s IF EXISTS

Question

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       gt  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

User · Answer

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"!

User · Answer

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

User · Answer

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

User · Answer

if exists select   from db1 table1 where sno 1   begin select   from db1 table1 where sno 1  end else if  select   from db2 table1 where sno 1   begin select   from db2 table1 where sno 1  end else begin print  the record does not exits  end

User · Answer

SELECT IF        SELECT count    FROM gdata calendars       WHERE  group       AND id       1 0     For Detail explanation you can visit here

[mysql] Usage of MySQL's "IF EXISTS"

Examples related to mysql