TL;DR
I need to select two rows and 2 columns from each row from Table Matches_teams and update one row and 4 columns in Table Matches.
My issue:
Consider this example: a football fantasy-like database. There are football teams, matches which must always include two different teams and each team will have odds
I have the following tables:
Table Matches:
Matches
--------
ID | date | team_winner_id | score
Then I created joint table Matches_teams. The following, for each match there it must always have 2 different teams with their respective odds
Matches_teams
--------------
ID | team_id | match_id | odds
Example records for the Matches_teams table:
ID | team_id | match_id | odds
-------------------------------
1 | 5 | 22 | 0.8
2 | 6 | 22 | 2.2
3 | 12 | 56 | 1
4 | 3 | 56 | 2.1
Now I realised that the joint table Matches_teams is not really needed. If a match can only and will always have two teams, I can add a column for each team and its odds in Matches table like this
Matches
--------
ID | team_a_id | team_a_odds | team_b_id | team_b_odds | date | team_winner_id | score
Using the above Matches_teams data as an example, I want my restructured Matches data to look like this:
Matches
ID | team_a_id | team_a_odds | team_b_id | team_b_odds | date | team_winner_id | score
---------------------------------------------------------------------------------------
22 | 5 | 0.8 | 6 | 2.2 | .. | ....
56 | 12 | 1 | 3 | 2.1 | .. | ....
Any MySQL operation which can do this? An operation which by a given match id, it will update Matches's existing records based on the data selected from Matches_teams and on the condition that Matches.id equals Matches_teams.match_id
