如何在 SQL Server 中使用 JOIN 执行 UPDATE 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1293330/
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
How can I do an UPDATE statement with JOIN in SQL Server?
提问by Ant Swift
I need to update this table in SQL Serverwith data from its 'parent' table, see below:
我需要使用其“父”表中的数据更新SQL Server 中的此表，请参见下文：
Table: sale
表：销售
id (int)
udid (int)
assid (int)
Table: ud
表：ud
id (int)
assid (int)
sale.assidcontains the correct value to update ud.assid.
sale.assid包含要更新的正确值ud.assid。
What query will do this? I'm thinking of a joinbut I'm not sure if it's possible.
什么查询会这样做？我正在考虑，join但我不确定是否可能。
回答by Eric
Syntax strictly depends on which SQL DBMS you're using. Here are some ways to do it in ANSI/ISO (aka should work on any SQL DBMS), MySQL, SQL Server, and Oracle. Be advised that my suggested ANSI/ISO method will typically be much slower than the other two methods, but if you're using a SQL DBMS other than MySQL, SQL Server, or Oracle, then it may be the only way to go (e.g. if your SQL DBMS doesn't support MERGE):
语法严格取决于您使用的 SQL DBMS。这里有一些方法可以在 ANSI/ISO（又名应该适用于任何 SQL DBMS）、MySQL、SQL Server 和 Oracle 中执行此操作。请注意，我建议的 ANSI/ISO 方法通常比其他两种方法慢得多，但如果您使用的是 MySQL、SQL Server 或 Oracle 以外的 SQL DBMS，那么它可能是唯一的方法（例如如果您的 SQL DBMS 不支持MERGE)：
ANSI/ISO:
ANSI/ISO：
update ud
set assid = (
select sale.assid
from sale
where sale.udid = ud.id
)
where exists (
select *
from sale
where sale.udid = ud.id
);
MySQL:
MySQL：
update ud u
inner join sale s on
u.id = s.udid
set u.assid = s.assid
SQL Server:
SQL 服务器：
update u
set u.assid = s.assid
from ud u
inner join sale s on
u.id = s.udid
PostgreSQL:
PostgreSQL：
update ud
set assid = s.assid
from sale s
where ud.id = s.udid;
Note that the target table must not be repeated in the FROMclause for Postgres.
请注意，目标表不能在FROMPostgres的子句中重复。
Oracle:
甲骨文：
update
(select
u.assid as new_assid,
s.assid as old_assid
from ud u
inner join sale s on
u.id = s.udid) up
set up.new_assid = up.old_assid
SQLite:
SQLite：
update ud
set assid = (
select sale.assid
from sale
where sale.udid = ud.id
)
where RowID in (
select RowID
from ud
where sale.udid = ud.id
);
回答by edosoft
This should work in SQL Server:
这应该适用于 SQL Server：
update ud
set assid = sale.assid
from sale
where sale.udid = id
回答by user1154043
postgres
邮局
UPDATE table1
SET COLUMN = value
FROM table2,
table3
WHERE table1.column_id = table2.id
AND table1.column_id = table3.id
AND table1.COLUMN = value
AND table2.COLUMN = value
AND table3.COLUMN = value
回答by MattH
A standard SQL approach would be
标准的 SQL 方法是
UPDATE ud
SET assid = (SELECT assid FROM sale s WHERE ud.id=s.id)
On SQL Server you can use a join
在 SQL Server 上，您可以使用联接
UPDATE ud
SET assid = s.assid
FROM ud u
JOIN sale s ON u.id=s.id
回答by alfonx
CREATE TABLE ud (id integer, assid integer);
CREATE TABLE sales (id integer, udid integer, assid integer);
UPDATE ud
SET assid = sales.assid
FROM sales
WHERE sales.id = ud.id;
回答by Vinit Kadkol
Simplified update query using JOIN-ing multiple tables.
使用JOIN-ing 多个表简化更新查询。
UPDATE
first_table ft
JOIN second_table st ON st.some_id = ft.some_id
JOIN third_table tt ON tt.some_id = st.some_id
.....
SET
ft.some_column = some_value
WHERE ft.some_column = 123456 AND st.some_column = 123456
Note- first_table, second_table, third_table and some_column like 123456 are demo table names, column names and ids. Replace them with the valid names.
注意- first_table、second_table、third_table 和 some_column 像 123456 是演示表名、列名和 ID。用有效名称替换它们。
回答by Yada
Another example why SQL isn't really portable.
另一个为什么 SQL 不是真正可移植的例子。
For MySQL it would be:
对于 MySQL，它将是：
update ud, sale
set ud.assid = sale.assid
where sale.udid = ud.id;
For more info read multiple table update: http://dev.mysql.com/doc/refman/5.0/en/update.html
有关更多信息，请阅读多表更新：http: //dev.mysql.com/doc/refman/5.0/en/update.html
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
回答by Ken Goh
I was thinking the SQL-Server one in the top post would work for Sybase since they are both T-SQL but unfortunately not.
我认为顶帖中的 SQL-Server 可以用于 Sybase，因为它们都是 T-SQL，但不幸的是不是。
For Sybase I found the update needs to be on the table itself not the alias:
对于 Sybase，我发现更新需要在表本身而不是别名上：
update ud
set u.assid = s.assid
from ud u
inner join sale s on
u.id = s.udid
回答by xhudik
Teradata Aster offers another interesting way how to achieve the goal:
Teradata Aster 提供了另一种实现目标的有趣方式：
MERGE INTO ud --what trable should be updated
USING sale -- from what table/relation update info should be taken
ON ud.id = sale.udid --join condition
WHEN MATCHED THEN
UPDATE SET ud.assid = sale.assid; -- how to update
回答by Luke Watts
MySQL
MySQL
You'll get the best performance if you forget the where clause and place all conditions in the ON expression.
如果您忘记 where 子句并将所有条件放在 ON 表达式中，您将获得最佳性能。
I think this is because the query first has to join the tables then runs the where clause on that, so if you can reduce what is required to join then that's the fasted way to get the results/do the udpate.
我认为这是因为查询首先必须加入表，然后在该表上运行 where 子句，所以如果您可以减少加入所需的内容，那么这是获得结果/执行 udpate 的快速方法。
Example
例子
Scenario
设想
You have a table of users. They can log in using their username or email or account_number. These accounts can be active (1) or inactive (0). This table has 50000 rows
你有一个用户表。他们可以使用他们的用户名或电子邮件或 account_number 登录。这些帐户可以处于活动状态 (1) 或非活动状态 (0)。该表有 50000 行
You then have a table of users to disable at one go because you find out they've all done something bad. This table however, has one column with usernames, emails and account numbers mixed. It also has a "has_run" indicator which needs to be set to 1 (true) when it has been run
然后，您可以一次性禁用一张用户表，因为您发现他们都做了坏事。但是，该表有一列混合了用户名、电子邮件和帐号。它还有一个“has_run”指示器，它在运行时需要设置为 1（真）
Query
询问
UPDATE users User
INNER JOIN
blacklist_users BlacklistUser
ON
(
User.username = BlacklistUser.account_ref
OR
User.email = BlacklistedUser.account_ref
OR
User.phone_number = BlacklistUser.account_ref
AND
User.is_active = 1
AND
BlacklistUser.has_run = 0
)
SET
User.is_active = 0,
BlacklistUser.has_run = 1;
Reasoning
推理
If we had to join on just the OR conditions it would essentially need to check each row 4 times to see if it should join, and potentially return a lot more rows. However, by giving it more conditions it can "skip" a lot of rows if they don't meet all the conditions when joining.
如果我们只需要在 OR 条件下加入，它本质上需要检查每一行 4 次以查看它是否应该加入，并可能返回更多的行。但是，通过给它更多条件，如果它们在加入时不满足所有条件，它可以“跳过”很多行。
Bonus
奖金
It's more readable. All the conditions are in one place and the rows to update are in one place
它更具可读性。所有条件都在一个地方，要更新的行在一个地方
