MySQL If variable equals then execute query

Question

I have a requirement in MySQL 5.7 to only run a query if a condition is true.

For example, below we have a variable called x. If it equals 8, we're OK to run the query.

I'm trying to use an IF statement for this

Can anyone tell me what's going wrong?

Thanks

SET @x = (select count(*) from (select distinct tbl  from db.tbl where dt = CURDATE())x);
    
IF @x = 8
   BEGIN
       SELECT * from db.tbl1
   END

Is this part of a stored procedure? If not: you can the "if" statement only inside a stored program (trigger, stored procedure, ...). — Solarflare
– Solarflare, Commented Sep 29, 2022 at 9:15
Hmm, this is not for repeatable jobs - it's to stop people running adhoc queries until all of our source tables have loaded. So we don't want loads of stored procedures — kikee1222
– kikee1222, Commented Sep 29, 2022 at 9:17
What is the error you are having? Shouldn't you add a space between the star and from in "SELECT *from db.tbl1"? — Sergio Prats
– Sergio Prats, Commented Sep 29, 2022 at 9:32
It's just the typical syntax error - the * was just a copy & paste issue into stack overflow, the issue persists irrespective of this — kikee1222
– kikee1222, Commented Sep 29, 2022 at 9:35
If that's your intention, you may need to add some details about your setup. E.g. if the users are using a mysql client directly, they would need to add this test manually to their queries anyway, right? Then you can just add a function "CheckIfAllSourceTablesAreLoadedAndICanRunAQueryNow()" that checks your condition and let user follow the result (e.g. the user executes the "IF" in their brain). If you have your own client/webpage/script/... that takes a query and sends it to the server, you could let that client check first if it wants to send it. Or something else, depending on your setup. — Solarflare
– Solarflare, Commented Sep 29, 2022 at 10:11

Bohemian · Accepted Answer · 2022-09-29 09:53:13Z

1

You don't need a procedure. Plain SQL will do:

SELECT * from db.tbl1
WHERE (select count(distinct col) from db.tbl where dt = CURDATE()) = 8

Note also the simplification using count(distinct ...)

answered Sep 29, 2022 at 9:53

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1