Correct syntax for SQL UPDATE where value to be set is a SELECT

Question

Hi folks

Can someone set me straight on the correct syntax for this statement?

I am trying to update a new field in table rounds with an average of a field in table holes where rounds.id is stored as a foreign key.

UPDATE `rounds` SET drivingDistance_avg_per_hole = 
SELECT SUM(holes.drivingDistance)/COUNT(holes.drivingDistance) as avg 
FROM `holes`, `rounds` 
WHERE holes.drivingDistance != '' 
AND holes.round = rounds.id 

Sure would appreciate the lesson!

TIA

JG

Answer 1

Depends on your database. Standard SQL doesn't have the FROM syntax with UPDATE and some databases don't support it. In that case this ought to work:

UPDATE `rounds` SET drivingDistance_avg_per_hole = (
SELECT SUM(holes.drivingDistance)/COUNT(holes.drivingDistance) as avg 
FROM `holes`
WHERE holes.drivingDistance != '' 
AND holes.round = rounds.id )

The key points are that the subselect has to be in parentheses, and that you don't want to include the destination table in the subselect because then you're selecting all rows of the table whereas you only care about the rows of holes that match the corresponding row from rounds.