SQL 使用内连接更新，更新两个表中的 2 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2149154/
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 with inner join, update 2 columns from both tables
提问by ijefr
This is my query in sql server 2008 -
这是我在 sql server 2008 中的查询 -
UPDATE a
SET a.col2 = 'new',
a.col3 = 'www.google.com',
b.col1 = '10'
FROM table a
INNER JOIN table b ON a.col1 = b.col1
WHERE a.col1 = 7
It crashes stating "Invalid column name b.col1."
它崩溃了“无效的列名 b.col1”。
How do I make this work?
我如何使这项工作？
回答by SQLMenace
You can only update 1 table at a time
一次只能更新 1 个表
you need to issue 2 update statements
您需要发出 2 个更新语句
UPDATE a SET a.col2='new', a.col3='www.google.com'
FROM tablea a INNER JOIN tableb b ON a.col1 = b.col1
WHERE a.col1=7
UPDATE b SET b.col1='10'
FROM tablea a INNER JOIN tableb b ON a.col1 = b.col1
WHERE a.col1=7
回答by Adriaan Stander
From looking at your query a little closer, you have b.Col1 in the UPDATE statement. this is incorrect
通过更仔细地查看您的查询，您可以在 UPDATE 语句中找到 b.Col1。这是不正确的
UPDATE a
SET a.col2='new',
a.col3='www.google.com',
b.col1='10'
FROM @table a INNER JOIN
@table b ON a.col1 = b.col1
WHERE a.col1=7
From UPDATEyou can only update 1 table at a time
从UPDATE 开始，您一次只能更新 1 个表
回答by Liz Albin
Your statement is "Update A", and you're trying to update a column in table B. You might want to create a view containing the columns in tables A and B, and update that. You could also create triggers for table A - perhaps one that will update the appropriate jointed column in table B.
您的语句是“更新 A”，并且您正在尝试更新表 B 中的一列。您可能希望创建一个包含表 A 和 B 中的列的视图，然后更新该视图。您还可以为表 A 创建触发器——也许会更新表 B 中适当的连接列。
回答by Skulkarni
For updating a column value in 1 table based on a condition on another column in second table, this worked for me:
为了根据第二个表中另一列的条件更新 1 个表中的列值，这对我有用：
UPDATE TableA
SET TableA.col1 = 'dummyVal'
WHERE TableA .ACCID IN (SELECT ACCID FROM TableB WHERE TableB.PRODID LIKE 'XYZ')
![SQL [转换为布尔值]](/res/img/loading.gif)