根据多个表的数据更新单个表 SQL Server 2005,2008
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16596552/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update a single table based on data from multiple tables SQL Server 2005,2008
提问by user1984533
I need to update table oneusing data from table two. Table one and two are not related by any common column(s). Table threeis related to table two.
我需要one使用table中的数据更新table two。表一和表二不与任何公共列相关。表three与表相关two。
Ex : table one(reg_det table)
例如：表一（reg_det 表）
reg_det_id | reg_id | results
101 | 11 | 344
table two :(temp table)
表二：（临时表）
venue | results
Anheim convention center | 355
Table three (regmaster-tbl)
表三（regmaster-tbl）
reg_id| venue
11 | Anaheim convention center
I need to update results column in table one using data from table two. But table one and two are not related. Table two and three and table one and three are related as you can see above. Can anyone please suggest any ideas! I need the results value to be 355 in table one and this data is coming from table 2, but these two are unrelated, and they can be related using table three. Sorry if it is confusing!
我需要使用表二中的数据更新表一中的结果列。但是表一和表二不相关。如上所示，表二和表三以及表一和表三是相关的。任何人都可以请提出任何想法！我需要表一中的结果值为 355 并且该数据来自表 2，但这两个是不相关的，它们可以使用表三相关联。对不起，如果它令人困惑！
回答by MBulava
Fairly straight forward:
非常坦率的：
UPDATE T1
SET result = t2.results
FROM [table one] T1
INNER JOIN [table three] t3
on t1.reg_id = t3.reg_id
INNER JOIN [table two] T2
on t2.venue = t3.venue
回答by Jesse Chisholm
Almost a question instead of an answer. :)
几乎是一个问题而不是一个答案。:)
Couldn't you use an implied inner join?
你不能使用隐含的内部连接吗？
UPDATE rd
SET rd.results = tt.results
FROM reg_det rd, regmaster rm, temptable tt
WHERE rm.reg_id = rd.reg_id
AND rm.venue = tt.venue;
I find it easier to read, and this syntax works in a SELECTstatement, with the same meaning as an explicit inner join.
我发现它更容易阅读，并且这种语法在SELECT语句中起作用，其含义与显式内部联接相同。
回答by criticalfix
Try this:
尝试这个：
UPDATE rd
SET rd.results = t.results
FROM reg_det rd
JOIN regmaster rm ON rm.reg_id = rd.reg_id
JOIN temptable t ON t.venue = rm.venue
WHERE t.results = 355
I added a WHERE clause because otherwise it will update all reg_det records that have matches in regmaster and temptable.
我添加了一个 WHERE 子句，否则它将更新所有在 regmaster 和 temptable 中匹配的 reg_det 记录。
