Simple Update Statement

[Jezternz]

UPDATE items i, cartitems c 
SET stock = (stock-1)
WHERE i.id = c.itemid AND c.userid = '7';
 

Okay so I have a table for 'items' and a table for 'items in a users shopping cart'. I want to decrement the stock field in the items table once for every instance of that same item in the 'items in shopping cart' table.

The code above, will unfortunately only decrement a maximum of once in stock per different item.

Any help would be greatly appreciated, and please don't suggest to add quantity fields to the 'items in shopping cart' table as this not an option.

Cheers, J

[Atli]

Hey.

You could use a dependent subquery. Basically, fetching the number of rows in the "items in shopping cart" table that match the ID of the item for each item and subtracting it from the existing number.

For example:

UPDATE items AS i
SET
    i.stock = i.stock - (
        SELECT     COUNT(*)
        FROM     user_items AS ui
        WHERE     ui.item_id = i.id
    );

Keep in mind that dependent subqueries can be resource hogs, but I assume this is going to be more of a rare maintenance thing, not something that is executed all the time?


You may also want to look into adding a Trigger to the "user_items" table, so that every time a row is added into it the items table is updated automatically.

[Jezternz]

Thanks for the quick response Atli,

As this is more for a trial then actual practical use, efficiency is not really a concern.
I would however be interested in an efficient one. If you have any links or anything that would help with writing a more efficient one, that would be great.

Cheers, J