query.sql

-- this select gets correct data
SELECT `rez_clubs`.`id`, `rez_clubs`.`name`, SUM(`rez_participants`.`points_1`)
FROM `rez_clubs`
JOIN `rez_participants` ON `rez_participants`.`club_id` = `rez_clubs`.`id`
GROUP BY `rez_participants`.`club_id`

-- what im trying to achieve
UPDATE `rez_clubs`
JOIN `rez_participants`
SET `rez_clubs`.`points_1` = SUM(`rez_participants`.`points_1`)
WHERE `rez_clubs`.`id` = `rez_participants`.`club_id`
GROUP BY `rez_clubs`.`id`

UPDATE `rez_clubs`
JOIN (
  SELECT `club_id`, SUM(`rez_participants`.`points_1`) AS points_sum
  FROM  `rez_participants` 
  GROUP BY `club_id`
) AS club_points
ON club_id = rez_clubs.id
SET rez_clubs.points_1 = club_points.points_sum