SQL Server：使用Select和Join两个表从一个表更新

I have two tables:

我有两张桌子:

Teams

Id  Name
1   Manchester
2   Arsenal
3   Inter
4   Milan

Games

GameId  FirstTeamId GoalsFirstTeam  GoalsSecondTeam SecondTeamId
1            1            0                 0             2
2            3            2                 3             4
3            1            1                 1             3

I want to update into GameResult with a select and join from two tables Games and Teams like this:

我想通过选择加入GameResult并从两个表中加入游戏和团队,如下所示:

GameId  FirstTeam   GoalsFT GoalsST SecondTeam
1       Manchester    0        0     Arsenal
2       Inter         2        3     Milan
3       Manchester    1        1     Inter

I tried this:

我试过这个:

UPDATE GameResult
SET    GameId = (SELECT GameId
                 FROM   Games),
       FirstTeam = (SELECT t1.Name AS FirstTeam
                    FROM   Games AS g
                           INNER JOIN Teams t1
                                   ON g.FirstTeamId = t1.Id),
       GoalsFT = (SELECT GoalsFirstTeam
                  FROM   Games),
       GoalsST = (SELECT GoalsSecondTeam
                  FROM   Games),
       SecondTeam = (SELECT t2.Name AS SecondTeam
                     FROM   Games AS g
                            INNER JOIN Teams t2
                                    ON g.SecondTeamId = t2.Id)

...but it didn't work.

......但它不起作用。

Can somebody help me with this ?

有人可以帮我这个吗?

3 个解决方案

#1

try this

UPDATE GameResult
SET GameId = G.GameId,
       FirstTeam = T1.Name,
       GoalsFT = G.GoalsFirstTeam,
       GoalsST = G.GoalsSecondTeam,
       SecondTeam = T2.Name 
 FROM Games G INNER JOIN Teams T1 ON G.FirstTeamID=T1.ID
        INNER JOIN Teams T2 ON G.SecondTeamId=T2.ID

UPDATE:

if you want just to insert new records into GameResult from the 2 other tables, try this

如果你只想从其他2个表中将新记录插入GameResult,试试这个

INSERT GameResult (GameId, FirstTeam, GoalsFT, GoalsST, SecondTeam)
SELECT G.GameId, T1.Name, G.GoalsFirstTeam, G.GoalsSecondTeam, T2.Name 
 FROM Games G INNER JOIN Teams T1 ON G.FirstTeamID=T1.ID
        INNER JOIN Teams T2 ON G.SecondTeamId=T2.ID

#2

It seems that your table 'GameResult' hasn't got any records to update. Either fill it first and then use the abovementioned solution by Pupa Rebbe, or use a Merge statement to insert, update and (if needed) delete records.

您的表'GameResult'似乎没有任何记录需要更新。先填充它然后使用Pupa Rebbe的上述解决方案,或使用Merge语句插入,更新和(如果需要)删除记录。

For more information, see this MS library

有关更多信息,请参阅此MS库

#3

Simple solution:

Delete From GameResult Go Insert GameResult (GameId, FirstTeam,GoalsFT, GoalsST, SecondTeam) SELECT g.GameId, t1.Name AS FirstTeam, g.GoalsFirstTeam, g.GoalsSecondTeam, t2.Name AS SecondTeam FROM Game g INNER JOIN Team t1 ON g.FirstTeamId = t1.Id INNER JOIN Team t2 ON g.SecondTeamId = t2.Id

从GameResult中删除Go插入GameResult(GameId,FirstTeam,GoalsFT,GoalsST,SecondTeam)SELECT g.GameId,t1.Name AS FirstTeam,g.GoalsFirstTeam,g.GoalsSecondTeam,t2.Name AS SecondTeam FROM Game g INNER JOIN Team t1 ON g .FirstTeamId = t1.Id INNER JOIN Team t2 ON g.SecondTeamId = t2.Id

#1