Apologies in advance for the confusing title, but couldn’t quite find the right way to summarize it in the title.
I have a table in SQLite Studio with four columns ID, Name, Tm (which means team) and TmID (TeamID) and 326 rows
314 Tyler Wade NYY NULL 315 Marcus Walden BOS NULL 316 Jared Walsh LAA NULL 317 Donovan Walton SEA NULL 318 Taylor Ward LAA NULL 319 Joey Wendle TBR NULL UPDATE TeamID SET TmID = 1 WHERE Tm = 'NYY';
Obviously the query above allows me to update each row where the team is “NYY”, but I couldn’t find a way to update all 15 teams in one big query (other than possibly transactions which are a bit cumbersome in SQLite Studio). I’d like to assign each team its own unique sequential ID number.
Thanks in advance.
Advertisement
Answer
that would be like this by using case
statement:
UPDATE TeamID SET TmID = case Tm when 'NYY' then 1 when 'BOS' then 2 when 'LAA' then 3 when 'SEA' then 4 when 'TBR' then 5 end