Determining values based on the count of that item in

Question

I have two tables. ticket & ticketlist. The sold out column in the ticket table needs to be 1 if that item is sold out. Table ticket needs to be soldout when the count for that item in table ticketlist is 2.

ticket
ticketid, quantity, soldout  
21          2             1 

ticketlist
ticketlistid, ticketid          
3                 21
4                 21

The logic is:

soldout should be '1' if ticket.quantity - (COUNT(ticketlist.ticketlistid) WHERE ticket.ticketid = ticketlist.ticketlistid) > 0

This is the MySQL that I tried

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

Any help will be appreciated.

Answer 1

In your subselect:

• You should only return one column.
• Don't select the same table you already have from your update.

You probably also want to set sold_out to one when quantity - (SELECT ...) <= 0, rather than > 0 as you are currently doing.

Change the query to this:

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

Also your database is denormalized. You are storing information in one table that can be derived from the data in another table. This redundancy can cause errors if the two ever get out of sync. I'd recommend only doing this if you need it for performance reasons.

Answer 2

One problem I see is this:

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

You are testing the result of that query against "> 0" however, it returns ticketid and count. You need to removed ticket.ticketid.

Answer 3

Try this:

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

Answer 4

You're better of implementing this as a view, otherwise risk the soldout number being out of sync.

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