CODEWITHSUNDEEP
mysql
Nich
Nich

Reputation: 283

Determining values based on the count of that item in

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.

Upvotes: 2

Views: 94

Answers (4)

Mark Byers
Mark Byers

Reputation: 838276

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.

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332581

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

Upvotes: 2

Kerry Jones
Kerry Jones

Reputation: 21838

Try this:

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

Upvotes: 1

dcp
dcp

Reputation: 55464

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.

Upvotes: 1

Related Questions