Skip to content
Advertisement

Update all rows with different values. When where conditions differ

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
4 People found this is helpful
Advertisement