SELECT, UPDATE, DELETE with one SQL query?

Question

I have a tiny operator that reduces the value:

UPDATE cart_items SET quantity = quantity - 1 WHERE cart_id = {$cart_id} AND id = {$cart_item_id} 

But is it possible for SQL to delete the row if this value becomes 0 after decrement? If so, then I want to recount the number of rows matching this cart:

 SELECT FROM cart_items WHERE cart_id = {$cart_id} 

And if the number of rows is zero, I want to delete this record from another table, for example:

 DELETE FROM cart WHERE id = {$cart_id} 

Currently, multiple queries require multiple queries, but can this be done in a single SQL statement?

Answer 1

The short answer is that this is not possible without wrapping additional requests inside a trigger or procedure.

You can do this in a transaction without SELECT , but it will take 3 queries:

 START TRANSACTION; UPDATE cart_items SET quantity = quantity - 1 WHERE cart_id = {$cart_id} AND id = {$cart_item_id}; DELETE FROM cart_items WHERE quantity = 0 AND cart_id = {$cart_id} AND id = {$cart_item_id}; DELETE c FROM cart c LEFT JOIN cart_items ci ON ci.cart_id = c.id WHERE c.id = {$cart_id} AND ci.cart_id IS NULL; COMMIT; 

The last DELETE connects the cart to cart_items and deletes the cart if it is not (field cart_items NULL ).

I included accessible identifiers to speed up DELETE s, although they should be fine without them .. it will just search and collect any other sum of 0 items or empty carts.

Answer 2

I think this cannot be done in a single request, even if you use triggers because you get an error message:

 CREATE TABLE cart_items (cart_id int key, quantity int); INSERT INTO cart_items VALUES (1, 1), (2, 2); -- Create trigger delimiter | CREATE TRIGGER update_cart_items AFTER UPDATE ON cart_items FOR EACH ROW BEGIN DELETE FROM cart_items WHERE quantity = 0; END; | delimiter ; 

And now, if you run the update request:

 UPDATE cart_items SET quantity = quantity - 1 WHERE cart_id = 1; 

You will receive an error message:

 ERROR 1442 (HY000): Can't update table 'cart_items' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 

That is why I think you should use multiple queries ...