Complicated SQL Update need help

I'm having trouble thinking how this would be pulled off in SQL (or if its even possible)
Here's the parts of the database you need to know:

Users:
--id
--money

Items:
--id
--moneygain

Inv(entory):
--id
--user_id
--item_id
--quantity

The script runs via cron job every 60 minutes and needs to globally update every users with their unique invtentory settings.
What I'm trying to do is update Users.money with the sum of Items.moneygain * Inv.quantity

What I have so far:

mysql_query("UPDATE users 
		JOIN (SELECT SUM(items.moneygain*inv.quantity) AS gainsum 
	                FROM items 
			JOIN inv ON inv.item_id=items.id)
		AS calc 
		SET users.money = calc.gainsum") or die(mysql_error());

PHP:

The problem with this is that it creates a sum of every moneygain and not just those specific to a user
*I can't use a where clause to choose a user with a specific id because I need to update them all at once (with a single query if possible) and without having to use a "totalmoneygain" field in Users table which will be added to and subtracted from every time a user receives an item..

 

There's no WHERE clause defining the select statement in the middle, so course that would happen. You'd either have to define a specific user, or somehow work the DISTINCT selector in there on a user id.

 

Do you have an idea of how a distinct query would work in this case?
As far as I know all it would do is select 1 row per user which is no good because users might have multiple items in the inventory table

 

What is the relationship between the tables, specifically the Inventory and Items tables?

Edit: Disregard, I misread the Inventory table.

 

Seems like hard work, can not help you.

 

Try something like this:

UPDATE Users
LEFT JOIN Inventory ON Users.id = Inventory.user_id
LEFT JOIN Items ON Inventory.item_id = Items.id
SET money = (Items.moneygain * Inventory.quantity)

 

If you have any ideas for how the query should look please post it, doesnt have to be based off mine because it's probably completely wrong hah

 

Correct, in which case you'd be better off looping each user (ie: doing a query to grab all the user ID, then doing a loop to run a query for each user)

In the end you quickly realize that 2 or 3 queries is far more practical than trying to get it all combined into one.

 

Thanks for helping but I tried this way and it only adds money based on the first inventory record for each user

 

It would be more practical to code but wouldn't using 2 or 3 queries for each user id (could be thousands) use up a huge amount of resources?

 

I see the problem. You could do it with a stored procedure. I don't think it's possible without being able to reference the user_id from the Inventory table.

 

Not much more than trying to make the SQL server handle that all in a single complex query. UPDATE/DELETE types of queries take up very little resources. And if you only pull the needed information from the user table (ie: just the id), you minimize the resource quite a bit, for example doing SELECT * FROM... on a few hundred thousand records, could take up a shitload of resources, but SELECT id FROM might only take up about a megabyte.

Also a few thousand is not really a big deal at all, not like we're dealing with a MS Access database that can only handle 64K records (Where as MySQL could handle a few million easily), just a matter of formating your database structure wisely, and only retrieving the information you need.