CODEWITHSUNDEEP
mysql
davewhirlwind
davewhirlwind

Reputation: 207

mySQL turning a SELECT with Join into an Update

After some well deserved comments that I was not supplying enough detail. I have rewritten my earlier deleted question.

I have two tables:

  1. race_results

    A list of the boats and skippers for each particular race in a series. There can be a bunch of them.

    race_id boat_id points skipper 1 45 3 John Doe 2 45 1 Jane Sailor 3 45 6 John Doe 1 51 2 Bob Fast 2 51 3 Bob Fast 3 51 4 Bob Fast

  2. series_results

This table has each race in the series plus race_id=99 record which is the overall place for all the races. (don't worry about the fact that the points are the same in both tables, there are cases where a different formula is used for the race and the series)

 series_id race_id  boat_id  points skipper
 98        1        45       3
 98        2        45       1
 98        3        45       6
 98        99       45       4
 98        1        51       2
 98        2        51       3
 98        3        51       4
 98        99       51       3

Both tables have column that is the name of the skipper. Interestingly the skipper can be different in each race. So what I want do is set the skipper name in the series_results table a concatenated skipper list. This query does that part nicely.

SELECT en.boat_id, GROUP_CONCAT(DISTINCT(skipper) SEPARATOR ' & ') as skipper 
FROM series_results sr  
LEFT JOIN race_results rr
ON rr.race_id =sr.race_id  AND re.boat_id=sr.boat_id 
WHERE sr.series_id='98'  
GROUP BY rr.boat_id

Here, I am stuck is how to convert this into the appropriate UPDATE of sr.skipper to the new concatenated version.
This is the end result I am looking for.

 series_id race_id  boat_id  points skipper
 98        1        45       3      John Doe & Jane Sailor
 98        2        45       1      John Doe & Jane Sailor
 98        3        45       6      John Doe & Jane Sailor
 98        99       45       4      John Doe & Jane Sailor
 98        1        51       2      Bob Fast
 98        2        51       3      Bob Fast
 98        3        51       4      Bob Fast
 98        99       51       3      Bob Fast

This UPDATE code gave me a syntax error at GROUP BY......

  UPDATE series_results sr  
  LEFT JOIN race_results rr
  ON rr.race_id =sr.race_id  AND re.boat_id=sr.boat_id
  SET sr.skipper= GROUP_CONCAT(DISTINCT(rr.skipper) SEPARATOR ' & ') 
  WHERE sr.series_id='98'  
  GROUP BY rr.boat_id

I tried a correlated subquery but that would only update race_id 1 in the series table. I would like all of them especially 99 to be updated.

Thanks for any help. Dave

Upvotes: 1

Views: 40

Answers (1)

forpas
forpas

Reputation: 164174

Join your select query to series_results:

UPDATE series_results s INNER JOIN ( 
  SELECT rr.boat_id, sr.series_id, 
    GROUP_CONCAT(DISTINCT(rr.skipper) SEPARATOR ' & ') as skipper 
  FROM series_results sr LEFT JOIN race_results rr
  ON rr.race_id =sr.race_id  AND rr.boat_id=sr.boat_id 
  WHERE sr.series_id = '98'  
  GROUP BY rr.boat_id, sr.series_id
) g ON g.boat_id = s.boat_id AND g.series_id = s.series_id
SET s.skipper = g.skipper;

See the demo.
Results:

| series_id | race_id | boat_id | points | skipper                |
| --------- | ------- | ------- | ------ | ---------------------- |
| 98        | 1       | 45      | 3      | Jane Sailor & John Doe |
| 98        | 2       | 45      | 1      | Jane Sailor & John Doe |
| 98        | 3       | 45      | 6      | Jane Sailor & John Doe |
| 98        | 99      | 45      | 4      | Jane Sailor & John Doe |
| 98        | 1       | 51      | 2      | Bob Fast               |
| 98        | 2       | 51      | 3      | Bob Fast               |
| 98        | 3       | 51      | 4      | Bob Fast               |
| 98        | 99      | 51      | 3      | Bob Fast               |

Upvotes: 1

Related Questions