Mysql IF statement Error on Nested if sentence

I am trying to create a procedure to update the stopTime field on my works table. The query I am using is:

update  mydb.works  set stopTime= 
(IF((SELECT table_name FROM information_schema.tables WHERE table_schema = 'mydb' AND table_name = 'results_24') is NULL, 
(select stampTime from mydb.works where id = 24), 
(IFNULL((select stampTime from mydb.results_24 order by id desc limit 1),
(select stampTime from mydb.works where id = 24))))) where id=24;

Shortly, the query should get the related results_ table stampTime as stopTime of works table’s related entry. If there is no table or empty table, the stampTime at the works table is used.

My problem occurs when there is no results_ table I’m looking for. The statement should check if related results_* table exists and if not, it should get the stampTime from works table.

But it also executes the statement below (i.e: ELSE part)

IFNULL((select stampTime from mydb.results_24 order by id desc limit 1)

and I got the error (as expected)

ERROR 1146 (42S02): Table 'mydb.results_24' doesn't exist

I checked the parenthesis and commas multiple times. I think there is no mistake about it. What am I missing? It shouldn’t execute the ELSE part, but it does. Why?

Thanks.

OS: Raspbian

Mysql version: Mysql Ver 15.1 Distrib 10.3.22-MariaDB, for debian-linux-gnueabihf (armv8l) using readline 5.2

Answer

Like GMB alsready said you need some dynamic sql

SELECT IF (EXISTS(SELECT 1 FROM information_schema.tables WHERE table_schema = 'mydb' AND table_name = 'results_24')
,@sql := 'update  mydb.works  set stopTime = IFNULL((select stampTime from mydb.results_24 order by id desc limit 1)
   ,(select stampTime from mydb.works where id = 24));'
   ,@sql := 'update  mydb.works  set stopTime = (select stampTime from mydb.works where id = 24);');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

Advertisement

Answer