CODEWITHSUNDEEP
mysqlsql
daninthemix
daninthemix

Reputation: 2570

SELECT, UPDATE, DELETE with one SQL query?

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?

Upvotes: 5

Views: 3942

Answers (2)

Arth
Arth

Reputation: 13110

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.

Upvotes: 2

cn0047
cn0047

Reputation: 17091

I think that it isn't possible to do all this in one query, even if you will use triggers, because you'll receive error:

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 will run update query:

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

You'll receive error:

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's why i think you should use few queries...

Upvotes: 0

Related Questions