Eugen M Eugen M - 4 months ago 8
SQL Question

MySQL Query IF with multiple statements

what im trying to do is a simple IF Condition within a MySQL command text, for example:

SET @var1=(SELECT ID
FROM table_a
WHERE ID=@ID_Parameter
ORDER BY ID ASC LIMIT 1);

IF @var1 IS NOT NULL THEN
BEGIN
DELETE FROM table_b
WHERE ID=@var1;

UPDATE table_c
SET ID=@var1
WHERE ID=@ID_Parameter;
END IF;


Obviously this just doesnt work, it complains that i have a sintax error inside my query, can ive searched google and found no way to use IF inside this specific case, any help appreciated.

Best Regards,
Eugen

Answer

I don't think you need IF at all. In your delete statement, if @var1 is null it evaluates to:

DELETE FROM table_b 
WHERE ID = NULL

Which won't delete any records (null = null isn't true). You can always add:

AND @var1 IS NOT NULL;

Which is unnecessary in this case, in other cases it is needed, like in the update statement below. It does add clarification to the function of the code, so may be good to add anyway.

You can change your update query to:

UPDATE table_c 
SET ID=@var1 
WHERE ID=@ID_Parameter
   AND @var1 IS NOT NULL;

With this change you can remove the if and get the functionality you request.

Comments