Sub query returns more than one row

Question

I'm building a point of sale software in php

So I've saved the items in the cart in a temporary table called tempsales, then update all the quantity of items with the new quantities. Just one query, using the code below which gives me subquery, returns:

More than one row error

UPDATE items SET quantity = quantity -
    (SELECT quantity FROM tempsales ORDER BY id ASC) 
WHERE id  IN 
    (SELECT id FROM ORDER BY I'd ASC)

The problem is that your subquery `(SELECT quantity FROM tempsales ORDER BY id ASC) ` returns more than a result, so you can't do the subtraction — ollaw, Jun 05 '17 at 21:45
I think this question addresses your problem. https://stackoverflow.com/questions/5727827/update-one-mysql-table-with-values-from-another — O. Jones, Jun 05 '17 at 21:49

score 0 · Accepted Answer · answered Jun 05 '17 at 21:53

It looks like you want to update a whole mess of rows in your items table based on the rows in your tempsales table. I think you want to match the id values. That requires using JOIN syntax in your UPDATE query, like this.

   UPDATE items
     JOIN tempsales ON items.id = tempsales.id
      SET items.quantity = items.quantity - tempsales.quantity

Sub query returns more than one row

1 Answers1