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
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
0
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
0
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
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
0
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
0
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