SQL 基于另一个表更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6023367/
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 table based on another table
提问by Bri
I'm trying to update a column in a table based on another column in another table.
我正在尝试根据另一个表中的另一列更新表中的一列。
UPDATE eval e
SET rank = (SELECT p.desc
FROM Position p
WHERE p.id = e.faculty
AND p.date >= '2011-05-20'
)
p.idand e.facultycorrespond. I want to update rank with p.descif the id's are the same. (e.facultyand p.id)
p.id并e.faculty对应。p.desc如果 id 相同，我想更新排名。(e.faculty和p.id)
Any help will be great! :)
任何帮助都会很棒！:)
回答by marc_s
Try this for SQL Server:
为 SQL Server 试试这个：
UPDATE dbo.eval
SET rank = p.desc
FROM dbo.Position p
WHERE p.id = eval.faculty and p.date >= '2011-05-20'
or if you need an alias on the base table (for whatever reason), you need to do this:
或者如果您需要基表上的别名（无论出于何种原因），您需要执行以下操作：
UPDATE dbo.eval
SET rank = p.desc
FROM dbo.eval e
INNER JOIN dbo.Position p ON p.id = e.faculty
WHERE p.date >= '2011-05-20'
回答by onedaywhen
You need a restriction in the form of a WHEREclause; if you use EXISTSyou can based it on you scalar subquery e.g.
你需要一个WHERE条款形式的限制；如果你使用EXISTS你可以基于你的标量子查询，例如
UPDATE eval
SET rank = (
SELECT p.desc
FROM Position p
WHERE p.id = eval.faculty
AND p.date >= '2011-05-20'
)
WHERE EXISTS (
SELECT *
FROM Position p
WHERE p.id = eval.faculty
AND p.date >= '2011-05-20'
);
Note the above targets the UPDATEon the base table evalrather than the correlation name e. This makes a lot more sense when you think of an SQL UPDATEin terms of relational assignment i.e. you don't want to assign to ebecause it (unlike the base table) will go out of scope!
注意上面的目标是UPDATE基表上的eval而不是相关名e。当您UPDATE从关系分配的角度考虑 SQL 时，这更有意义，即您不想分配给，e因为它（与基表不同）将超出范围！
