Update table using join and conditions(where) from another table

Question

I have two tables: Teams and Players.

Teams: TeamID, NumberOfPlayers, ..

Players: PlayerID, Name, Surname, TeamID, ..

I want to update NumberOfPlayers according to a player's Name, Surname and TeamID.

This is what I tried:

UPDATE Teams 
SET NumberOfPlayers = 28 
FROM Teams 
JOIN Players ON Teams.TeamID = Players.TeamID
WHERE Players.Name LIKE 'Amata' AND Players.Surname LIKE 'Walton' AND Players.TeamID = 3

But I get an error:

near "FROM": syntax error: UPDATE Teams 
SET NumberOfPlayers = 28 
FROM

I also tried:

UPDATE Teams 
JOIN Players ON Teams.TeamID = Players.TeamID
SET NumberOfPlayers = 28 
WHERE Players.Name LIKE 'Amata' AND Players.Surname LIKE 'Walton' AND Players.TeamID = 3

And the error I get is:

near "JOIN": syntax error: UPDATE Teams 
JOIN

Another shot was this:

UPDATE Teams AS T
JOIN Players P ON T.TeamID = P.TeamID
SET T.NumberOfPlayers = 28 
WHERE P.Name LIKE 'Amata' AND P.Surname LIKE 'Walton' AND P.TeamID = 3

Which led me to this error:

near "AS": syntax error: UPDATE Teams AS

It seems like the problem is after UPDATE Teams, but that's a valid table. Is this possible? What am I missing?

Answer 1

Your code is a bit odd, because you already know the count and the team ID you want to update, so I'm not sure why the player matters.

Further, I'd be concerned that it sorta looks like you might be using a firstname/lastname as a key, and I'd strongly not recommend that. Also, if you want an exact match on a string, = is referenced. See details on that here: Equals(=) vs. LIKE

But I believe this is the query you are technically going for.

Some relational databases support a FROM/JOIN on a update (like SQL Server), so don't (Like Oracle). SQLite doesn't support that syntax.

UPDATE Teams 
SET NumberOfPlayers = 28 
Where TeamId In
(SELECT TeamId From Players
WHERE Players.Name LIKE 'Amata' AND Players.Surname LIKE 'Walton' AND Players.TeamID = 3
)