I have a query that deactivates all expired items. When an item is deactivated I also need to decrement the appropriate category's item count. I can do this by iterating the items programatically outside of SQL, but I would like to do it the "proper" way with a single query making MySQL do all the work.
Here's the query that deactivates expired items:
UPDATE items SET status=3 WHERE status=1 AND NOW()>enddate
So for each item that is deactivated I also need to do this:
UPDATE categories SET count=count+1 WHERE id=$targetid LIMIT 1
$targetid would be the category id of the item that was deactivated.
Now I've done work in selecting values from multiple tables, via joins and the like, but I'm not sure how to go about updating values in multiple tables at the same time. Hopefully someone can save me some trial and error.
![Smile :)](./images/smilies/icon_smile.gif)
Thanks.
Dan East