Usage of MySQL's "IF EXISTS"
MysqlMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
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 = ?)
Solution 2 - Mysql
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;
Solution 3 - Mysql
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"!
Solution 4 - Mysql
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
.
Solution 5 - Mysql
SELECT IF((
SELECT count(*) FROM gdata_calendars
WHERE `group` = ? AND id = ?)
,1,0);
For Detail explanation you can visit here
Solution 6 - Mysql
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