MySQL query for updating order counts

[jdiver]

I have an ordering system in place and one of the fields for customers is used to store the number of orders they've placed. This field occasionally gets out of sync due to orders being voided, deleted, etc, so I occassionally need to run a program to resync the order counts.

Right now I'm running one query to pull all the users, then looping through each user and using another query to count their number of orders, then running a final query to update the "num_orders" field before moving to the next user. Needless to say with 20,000+ users, it's not very efficent. Without having MySQL 4.1 to do subqueries, is there any more efficient way to do this? Any thoughts are greatly appreciated. TIA.

The two tables tables used are (showing only relevant fields):

users
- user_id
- num_orders

orders
- order_id
- user_id

[jheitz]

Best option would have not to have such a program. Is it not an option to modify the process where orders are deleted / voided so that the user record is updated?

Failing that, you could use 1 cursor that goes through all USERS and then UPDATES the NUM_ORDERS. It should be quite quick, even with 20,000+. Although, you failed to mention with what frequency you are planning to run the script (ie: overnight, every hour, weekly,...?)

The SQL should be like this (this is pseudocode):

DECLARE CURSOR X FOR SELECT user_ID FROM users;

OPEN X
GET VALUE FORM X INTO UserVar

WHILE THERE ARE ITEMS IN THE CURSOR
    UPDATE users SET num_orders = (SELECT count(*) FROM orders WHERE User_ID=UserVar;

GET VALUE FORM X INTO VARIABLE
END WHILE

CLOSE X

Hope that helps