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

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

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

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

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

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

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

Examples related to mysql