Query Optimization - UPDATE value with SUM from another table

[big_nerd]

Hello All,

Although I am sure there are a million better ways to design a DB, im taking over someone else' work.

We're trying to generate a report (we don't have a BI Platform yet...)

To do so we're a temporary table, and selecting all users into it (yes, this works fine).

Then we're doing:

$users = array(); // assume this is populated with user id's.

foreach ($users as $user) {
	$query = "SELECT sum(`amount`) as `everything` FROM `moneytable` WHERE `blah` = 'blah' AND `user_id` = ".$user['id'];
	$result = mysql_query($query);
	$cash = mysql_fetch_assoc($result);
	$query = "UPDATE `report` SET `cashmoney` = '".$cash['everything']."' WHERE `id` = ".$user['id'];
}

Note: This is the over simplified version just because its easier.

I would like to set the SUM(amount) from the moneytable and update the report table with that SUM value, per user id (that ID is available in both tables existing)

Any ideas would be grand..

[bradgrafelman]

UPDATE
	report r
		LEFT JOIN (
			SELECT user_id id, SUM(amount) sum_amount
			FROM moneytable
			WHERE blah = 'blah'
			GROUP BY user_id
		) AS m
		USING (id)
SET r.cashmoney = m.sum_amount;

However, if you really wanted to optimize it, then you could just do it all in one query after creating the 'report' table (and/or truncating it):

INSERT INTO report (id, cashmoney)
		SELECT user_id, SUM(amount)
		FROM moneytable
		WHERE blah = 'blah'
		GROUP BY user_id

[big_nerd]

bradgrafelman,

Thanks for this, I am running a test on it now to see how she fares.

Ill mark as resolved (i hope!) once its done.