sql - Inserting sqlite data based on join and case

Question

I have 2 tables: weekly_scores and team in a sqlite database (SQLite Version 3.32.2)

• Weekly_scores has team_id and a goal_differential that contains the number of goals that the team won by that week. team has team.id for different teams
• I want to categorize teams in the team table based on the maximum winning goals during the season.
  • If goal_differential < 2, then team.skill = low
  • If goal_differential >= 2, then team.skill = high

I want to return the maximum goal differential for each team in the weekly_scores table and categorize the skill in the team table by the above criteria.

I based my code off of this answer and this website

insert into team(skill)
values(
case 
    select max(weekly_scores.goal_differential) as max_count
    from weekly_scores
    left join team
    on weekly_scores.team_id = team.id
    where weekly_scores.goal_differential is not NULL
    group by team.id;
when max_count < 2 then team.skill = low
when max_count >= 2 then team.skill = high
end);

I get the following error

Result: near "select": syntax error  
At line 8:  
insert into team(skill)  
values(  
case   
    select

Any help would be great!

question from:https://stackoverflow.com/questions/65600684/inserting-sqlite-data-based-on-join-and-case

Answer 1

You need to update the table team and not insert new rows.
Use a correlated subquery:

update team 
set skill = coalesce(
  (
    select case when max(w.goal_differential) < 2 then 'low' else 'high' end 
    from weekly_scores w
    where w.team_id = team.id and w.goal_differential is not NULL 
  ), 
  skill
)