StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POError #1242 Subquery returns more than 1 row when update
text
Body
copied!<p>This was my previous <a href="https://stackoverflow.com/questions/18537760/how-can-i-get-distinct-group-by-behavior-in-update-query/18542231?noredirect=1#18542231">question</a>. I can't solve problem. I update my code and, I use <code>GROUP BY</code> indirectly. but same problem shows. subquery return duplicate values. Actually I made a TRIGGER, So I think if show trigger, it will be clear to understand</p> <pre><code>-- -- Triggers `product_purchases` -- DROP TRIGGER IF EXISTS `change_stock_when_purchases`; DELIMITER // CREATE TRIGGER `change_stock_when_purchases` AFTER INSERT ON `product_purchases` FOR EACH ROW BEGIN DECLARE X INTEGER; SET X = (SELECT product_size FROM product_stock WHERE product_id = NEW.product_id ); IF NEW.product_size =X THEN UPDATE product_stock as S1 INNER JOIN ( SELECT S.product_stock_id,I.quantity FROM product_stock AS S INNER JOIN product_purchase_item AS I ON S.product_id=I.product_id AND S.product_size=I.product_size INNER JOIN product_purchases AS P ON I.product_purchase_item_id=P.product_purchase_item_id WHERE P.product_purchase_id=NEW.product_purchase_id GROUP BY S.product_stock_id ) AS M ON S1.product_stock_id=M.product_stock_id SET S1.product_size_quantity=S1.product_size_quantity+M.quantity; ELSE INSERT INTO product_stock (product_id,product_size,product_size_quantity) SELECT product_id,product_size, quantity FROM product_purchase_item WHERE product_purchase_item_id=NEW.product_purchase_item_id; END IF; END // DELIMITER ; </code></pre> <p>Problem show when it update same <code>product_id</code>'s different <code>product_size</code> on <code>product_stock</code> </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload