CODEWITHSUNDEEP
sqlsqlitesql-update
GDolan
GDolan

Reputation: 47

Filling column with data from another column in the same table

I am new to SQL and this is probably fairly easy but I cannot figure out how to make it work.

I am trying to fill a column with data pulled from another column in the same table.. in this case its a basketball database with box scores and I am trying to fill the column of opponent points (opp_pts) to match what their opponent for that game scored. each game is matched by season_id and game_id.

the whole table is about 700 rows with a few hundred games and about 40 teams but a sample is below... this is an example of one game where the score was 84-81 but I want to fill opp_team_stats with the appropriate score 

season_id   game_id   team_id     team_pts      opp_team_pts    
  U2018       140    U2018_19      84.0                         
  U2018       140    U2018_23      81.0

I have tried but have only been able to fill the whole column of opp_team_pts with with 84 which is obviously incorrect 

UPDATE box_scores 
SET opp_team_pts = (SELECT box_scores.team_pts
FROM box_scores
WHERE box_scores.season_id=box_scores.season_id AND box_scores.game_id=box_scores.game_id);

I'm sure the code is probably redundant but that is as far as I got, I understand why it filled the way it did but can't seem to figure out how to fix it... I may be on the wrong track but hopefully can get a bit of help

Upvotes: 3

Views: 1057

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

Assuming that each game has exactly two teams, you can use a correlated subquery:

UPDATE box_scores
    SET opp_team_pts = (SELECT bs2.team_pts
                        FROM box_scores bs2
                        WHERE bs2.season_id = box_scores.season_id AND
                              bs2.game_id = box_scores.game_id AND
                              bs2.team_id <> box_scores.team_id
                       );

SQLite does not support FROM in the UPDATE statement.

Upvotes: 2

Related Questions