我认为您需要一个UPDATE语句,其中包含两个表的连接,并使用CASE表达式将值设置为列:
UPDATE controller c
INNER JOIN assignuser au
ON c.idno = au.assignedidno
SET c.access = CASE
WHEN (date(au.expiration) < date(now())) THEN '0'
ELSE '1'
END
可以这样简化:
UPDATE controller c
INNER JOIN assignuser au
ON c.idno = au.assignedidno
SET c.access = (date(au.expiration) >= date(now()))
