MySQL - IF EXISTS

Hi y'all,

IF (SELECT COUNT(*) FROM tbl_name WHERE category_code ='some-category-code') THEN UPDATE tbl_name SET active='0' WHERE category_code = 'some-category-code' END IF

Why won't the above work? I know the update part works by itself. The select count part also works by itself.

Thanks for any help.

Rob

 

Maybe I'm asking the wrong question. I need to be able to update a row if row exists. I say this because if I blindly try to update a row and it doesn't exist, it stops the batch cold.

Ideas? =)

 

You probably need to use a stored procedure or use several queries in your actual script (assuming this is being run from a website).

 

you may try to use REPLACE

 

UPDATE table SET active = IF(category_code ='some-category-code', 0, active);

 

Thanks for the replies. This UPDATE table SET active = IF(category_code ='some-category-code', 0, active); isn't quite what I'm looking for as it will set a category active if it's not "the" category (which may have already been set to inactive). Works great if it is the category, just that little side effect.

Thanks again.

Rob

 

If you copied the query exactly, then the most likely culprit is that you are not setting the if qualifier. Try:

IF (SELECT COUNT(*) FROM tbl_name WHERE category_code ='some-category-code') > 0 THEN UPDATE tbl_name SET active='0' WHERE category_code = 'some-category-code' END IF

(notice the > 0 part that was added)

The post title says if exists, but you are just using an if statement, not an if exists. If you want to use the exists function, then the query should look like:

IF EXISTS(SELECT * FROM tbl_name WHERE category_code ='some-category-code') THEN UPDATE tbl_name SET active='0' WHERE category_code = 'some-category-code' END IF

(notice the count function has been removed)

The reason you would remove the count function is that count will always returns a value so it will always exist.

 

Sorry about the misleading post title. I'd tried many different approaches including if exists, but could get none to work. Obviously I wasn't thinking clearly (read as forgot to actually proofread my post). Thanks, I will try both suggestions.

Rob

 

Simple, without knowing what you are trying to do, something like this would work:

 

IF EXISTS(SELECT * FROM tblusers WHERE LoginName = 'superadmin') THEN
SET @a = 1;
ELSE
SET @a = 2;
END IF;
SELECT @a

Hi I am New to MySql was working on MSSQL Can any one help me in whats wrong in the above code. The same code will work in MSSQL.

Thanks in Advance
Girish