CODEWITHSUNDEEP
mysqlcountcase
elstiv
elstiv

Reputation: 377

MySQL count used in a case query statement

I require this query to execute a CASE statement depending on the value returned by COUNT:

select   @getshift :=  (select(COUNT(*) from `oee_machinenames` where `Shift1` = '06:00:00'))
(Case
When  @getshift >=1 
Then
update `oee_system`.`oee_machinenames` 
set `oee_machinenames`.`default_GM_state` = 0 
where `oee_machinenames`.`Shift1` = '06:00:00'
when @getshift ==0 
then
update `oee_system`.`oee_machinenames` 
set `oee_machinenames`.`default_GM_state` = 9 
where `oee_machinenames`.`Shift1` = '06:00:00' end)

The variable @getshift should return the number of count and if this >= 1 then an update statement occurs, if not then another update statement should occur. I will eventually be using this in a MYSQL time triggered event

EDIT: Neither this works as a stored procedure:

CREATE DEFINER=`tpm_dev`@`%` PROCEDURE `getshifts`()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
select  @getshift := select COUNT(*) from oee_machinenames where oee_machinenames.Shift1 = '06:00:00';
update oee_machinenames
if @getshift >=1 
set default_GM_state = 99;
else
set default_GM_state = 90;
end if
END

Upvotes: 0

Views: 87

Answers (1)

Kickstart
Kickstart

Reputation: 21513

You can possibly do a join to a sub query. However you are updating the same table as the sub query uses and MySQL doesn't support that. You can maybe get away with putting an extra sub query to select the results from the sub query to hide the common table from MySQL.

Something like this:-

UPDATE oee_system.oee_machinenames a
INNER JOIN 
(
    SELECT aCount
    FROM
    (
        SELECT COUNT(*) AS aCount
        FROM oee_machinenames 
        WHERE Shift1 = '06:00:00'
    ) c
) b
set a.default_GM_state = IF(b.aCount = 0, 0, 99) 
WHERE b.aCount >= 0
AND a.Shift1 = '06:00:00'

Upvotes: 1

Related Questions