mySQL Update column checking other field value-CodePudding

i have my app configuration values in table for example.

id   appid   keyValue   dataValue   
1    app1    hitcount       3
2    app1    lasthit    2022-03-23 13:15:56

i was trying to write a query in MySQL to reset hitcount = 0 if lasthit date change for example if 23 changed to 24 i want to reset 0 hitcount i wrote this query but getting error [Error Code: 1093.].

  update appconfig 
  SET dataValue = IF ( (select day(dataValue) 
                        from appconfig 
                        where appid  = 'app1' 
                        and keyValue   = 'lasthit'
                        ) > day(utc_timestamp()),0,dataValue
                      )
where appid  = 'app1' 
and keyValue   = 'hitcount'

Please advice

CodePudding user response：

You can use an INNER JOIN

But such a Query belongs in an EVENT HANDLER

CREATE TABLE appconfig (
  `id` INTEGER,
  `appid` VARCHAR(4),
  `keyValue` VARCHAR(8),
  `dataValue` VARCHAR(10)
);

INSERT INTO appconfig
  (`id`, `appid`, `keyValue`, `dataValue`)
VALUES
  ('1', 'app1', 'hitcount', '3'),
  ('2', 'app1', 'lasthit', '2022-03-23'),
    ('3', 'app2', 'hitcount', '3'),
  ('4', 'app2', 'lasthit', '2022-03-22');

UPDATE appconfig a1
INNER JOIN (SELECT `appid` FROM appconfig WHERe DATE(`dataValue`) < current_date() 
AND `keyValue` = 'lasthit' ) a
ON a.`appid` = a1.`appid`
SET 
    dataValue = 0
WHERE
 keyValue = 'hitcount'

SELECT * FROM appconfig

id | appid | keyValue | dataValue 
-: | :---- | :------- | :---------
 1 | app1  | hitcount | 3         
 2 | app1  | lasthit  | 2022-03-23
 3 | app2  | hitcount | 0         
 4 | app2  | lasthit  | 2022-03-22

db<>fiddle here