0

Hello I have a cart and it has several items, am trying to update the inventory so that all items in the inventory are updated when a buyer checks out..so it would subtract all the hardcover, softcover and ebooks from inventory depending on the purchases..I know how to make it work when I just need to update one record the question is how do I update several rows... Here is my current sql..

UPDATE inventory INNER JOIN cart ON inventory.isbn = cart.isbn 
SET inventory.num_hardcover = inventory.num_hardcover - cart.hardcover_purchased 
WHERE inventory.isbn=cart.isbn
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Will Barangi
  • 145
  • 1
  • 2
  • 13
  • Similar question was already been asked, check here http://stackoverflow.com/questions/20255138/sql-update-multiple-records-in-one-query – genesst Nov 08 '16 at 00:09
  • Is this query working at all? My first thought would be to update the value based on a subquery, i.e. `UPDATE inventory i SET num_hardcover = num_hardcover - (SELECT sum(hardcover_purchased) FROM cart c WHERE c.isbn = i.isbm)` I'm not at my database server, I may be able to test later. – K Richardson Nov 08 '16 at 00:12
  • @Bill Karwin...thank you – Will Barangi Nov 08 '16 at 00:13

1 Answers1

0

Finally figured it oout

UPDATE inventory 
INNER JOIN cart ON inventory.isbn=cart.isbn 
SET inventory.num_hardcover =inventory.num_hardcover-cart.hardcover_purchased,
    inventory.num_softcover=inventory.num_softcover-cart.softcover_purchased 
WHERE inventory.isbn=cart.isbn
Barmar
  • 741,623
  • 53
  • 500
  • 612
Will Barangi
  • 145
  • 1
  • 2
  • 13
  • You don't need the `WHERE` clause, since it's the same as the `ON` clause. – Barmar Nov 08 '16 at 00:56
  • 1
    I don't see the difference between this query and the original one in the question. They both update all rows that match the `JOIN` condition. The only difference is that this one updates multiple columns, but your question was about updating multiple rows. Are you confused about the difference between rows and columns? – Barmar Nov 08 '16 at 00:57
  • @Barmar yeah I wasn't sure about how to go about editing multiple rows...But I got it...thanks – Will Barangi Nov 08 '16 at 03:48
  • I still don't understand the answer. This edits the same rows as the query in the original question. The original query edits 1 column, this edits 2 columns. – Barmar Nov 08 '16 at 03:49