MySQL help

[Dan East]

I've got two tables, items and categories. Each item has a reference to the category it belongs to. Now for performance reasons I want each category to store a count of the number of items it contains.

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.

Thanks.

Dan East

[David Horn]

Shouldn't it be SET count=count-1, or have I got the wrong end of the stick here?

[Dan East]

No, you're right.

Dan East

[refractor]

[David Horn]

[David Horn]

[Dan East]

Thanks for the assistance guys. I'm going to just retotal them all anytime a change is made, since changes will be relatively seldom.

The version of MySQL we're using doesn't support nested queries, so I'm iterating the categories in php for now.

Dan East