can you update using a GROUP BY in mysql

[sfullman]

here's the query:

UPDATE
epld_ContractsRoomblocks a,
epld_hotels_roomblocks b,
epld_reservations_rooms_days c,
epld_reservations_rooms d,
epld_reservations e
SET a.Private=SUM(d.Private)
WHERE a.Roomblocks_ID=b.ID AND
c.Contractsroomblocks_ID=a.ID AND
d.ID=c.Rooms_ID AND
e.ID=d.Reservations_ID
GROUP BY a.ID

by doing a certain group by query, I get a valuable piece of info as to a "SUM" of 1 flags in d.Private. a.Private, which is NOT strictly normalized but does make some of my queries faster, must at all times = the sum of d.private=1 flags present. I thought this would work but it craps out at the GROUP BY clause.

Possible? Possible in strict ANSI SQL? thanks!! :-)

[tomhath]

Subselect is your friend.

Untested:

UPDATE epld_ContractsRoomblocks 
SET Private=sumprivate
FROM epld_ContractsRoomblocks a
INNER JOIN (
	SELECT a.ID, SUM(d.Private) as  sumprivate
	FROM epld_ContractsRoomblocks a
	INNER JOIN epld_hotels_roomblocks b
		ON  a.Roomblocks_ID=b.ID
	INNER JOIN epld_reservations_rooms_days c 
		ON c.Contractsroomblocks_ID=a.ID
	INNER JOIN epld_reservations_rooms d 
		ON d.ID=c.Rooms_ID
	INNER JOIN epld_reservations e
		ON e.ID=d.Reservations_ID
	GROUP BY e.ID=d.Reservations_ID
	) as b
	ON a.ID = b.ID