How to select certain rows

I want to create a promotion and relegation between two leagues where the top two teams from LeagueID 2 moves up to LeagueID 1 and the bottom two teams from LeagueID 1 moves down to LeagueID 2.

My question is how to select those teams, bearing in mind the number of team per league may change in the future, so I cannot for relegating teams use a where clause on position. I can use a where clause for promotion no problem for position 1 and 2 from LeagueID 2 but if there is a way to do it from a select and a count then I will go for that method as well.

I know all I need to do when we select the correct teams is just update their LeagueIDs.

LATEST UPDATE:

UPDATE dbo.League
SET LeagueId = 2
WHERE LeagueId = 1
AND Position IN
(
SELECT TOP 2 Position
FROM dbo.League_Table
WHERE LeagueID = 1
ORDER BY Position DESC
)

UPDATE dbo.League
SET LeagueId = 1
WHERE LeagueId = 2
AND Position IN
(
SELECT TOP 2 Position
FROM dbo.League_Table
WHERE LeagueID = 2
ORDER BY Position
)

'Team' Table: This table is a reference for all teams and which league they play in:

You can order descending for the bottom two, no matter how many there are

SELECT TOP 2 *
FROM Teams
WHERE LeagueID = 1
ORDER BY Position DESC

(Sidenote: Im hoping your Position field is calculated, because if you maintain it that could affect the integrity of your data)

Your 2 queries are fine, and combining them into one, although possible, will be less clear. You're simply missing a WHERE clause in the subqueries

UPDATE dbo.League
SET LeagueId = 2
WHERE LeagueId = 1
AND Position IN
(
SELECT TOP 2 Position
FROM dbo.League
WHERE LeagueID = 1
ORDER BY Position DESC
)
UPDATE dbo.League
SET LeagueId = 1
WHERE LeagueId = 2
AND Position IN
(
SELECT TOP 2 Position
FROM dbo.League
WHERE LeagueID = 2
ORDER BY Position
)

Another try at this - this time with a single query. H/T to @Jens for providing a test case.

If you first work out how every team's league position changes:

SELECT LeagueID, TeamName, case
when league>1 AND RANK() OVER (PARTITION BY league ORDER BY position ASC) <= 2 THEN -1
when league<2 AND RANK() OVER (PARTITION BY league ORDER BY position DESC) <= 2 THEN 1
else 0
end as leaguechange
FROM dbo.League_Table

(Note I have put in an extra 2 clauses there - league>1 to stop a team being promoted to league 0 and league<2 to stop teams being demoted to league 3 - both of these can be adjusted accordingly)

Once you have that, it can be used as a subquery within a single UPDATE:

UPDATE dbo.Team
SET LeagueID = LeagueID + leaguechange
FROM (
SELECT LeagueID, TeamName, case
when league>1 AND RANK() OVER (PARTITION BY leagueID ORDER BY position ASC) <= 2 THEN -1
when league<2 AND RANK() OVER (PARTITION BY leagueID ORDER BY position DESC) <= 2 THEN 1
else 0
end as leaguechange
FROM dbo.League_Table
) c
INNER JOIN dbo.Team t
ON c.LeagueId = t.LeagueID
AND c.TeamName = t.TeamName