基于中该项的计数确定值

在您的子选择中:

你可能还想 sold_out quantity - (SELECT ...) <= 0 ,而不是 > 0

将查询更改为:

UPDATE ticket
SET soldout = '1'
WHERE quantity - (
    SELECT COUNT(ticketlist.ticketlistid)
    FROM ticketlist
    WHERE ticket.ticketid = ticketlist.ticketid
) > 0;

你比我强 implementing this as a view ,否则可能导致soldout号不同步。

CREATE VIEW vw_tickets AS
    SELECT t.ticketid,
           t.quantity,
           COUNT(*) AS sold,
           CASE 
             WHEN t.quantity = COUNT(*) THEN 1 
             WHEN t.quantity < COUNT(*) THEN -1 -- oversold
             ELSE 0 
           END AS soldout
      FROM TICKET t
 LEFT JOIN TICKETLIST tl ON tl.ticketid = t.ticketid
  GROUP BY t.ticketid, t.quantity

(SELECT ticket.ticketid, COUNT(ticketlist.ticketlistid)
            FROM ticket, ticketlist
            WHERE ticket.ticketid = ticketlist.ticketid) 

您正在对该查询的结果进行测试 "> 0" 但是,它返回ticketid和count。您需要删除ticket.ticketid。

UPDATE `ticket`
SET `soldout` = 1
WHERE `ticketid` IN ( 
    SELECT `ticketid` FROM `ticketlist` GROUP BY `ticketid` HAVING COUNT(`ticketlistid`) = 2 )