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
Aucun commentaire:
Enregistrer un commentaire