Page 1 of 1

MySQL help

PostPosted: Oct 17, 2005 @ 2:04pm
by 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

PostPosted: Oct 17, 2005 @ 2:34pm
by David Horn
Shouldn't it be SET count=count-1, or have I got the wrong end of the stick here? ;)

PostPosted: Oct 17, 2005 @ 2:43pm
by Dan East
No, you're right. :)

Dan East

PostPosted: Oct 17, 2005 @ 3:51pm
by refractor

PostPosted: Oct 18, 2005 @ 11:01am
by David Horn

PostPosted: Oct 21, 2005 @ 7:57pm
by David Horn

PostPosted: Oct 21, 2005 @ 9:17pm
by 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