SELECT, UPDATE, DELETE with one SQL query?

Question

I have a tiny statement which decrements a value:

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

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

SELECT FROM cart_items WHERE cart_id = {$cart_id}

And if the number of rows is zero, I want to delete that record from another table, like so:

DELETE FROM cart WHERE id = {$cart_id}

At the moment it seems like a need several queries to do this, but could it all be done in a single SQL statement?

Answer 1

Short answer is that it's not possible without wrapping the extra queries inside a trigger or procedure.

You can do this in a transaction, and without a 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 joins cart to cart_items, and deletes the cart if none are found (cart_items fields are NULL).

I have included available identifiers to speed up the DELETEs, although they should be fine without them.. it'll just look for and pick up any other quantity 0 items or empty carts.