SQL UPDATE 与 JOIN：工作原理

在关系型数据库中，一致的数据对于可靠性和完整性至关重要。当需要跨关联表修改现有数据时，我们会结合使用 UPDATE 语句与 JOIN 子句来实现数据一致性。SQL Server 支持带有 JOIN 的 UPDATE 操作，这有助于确保跨表的数据精确同步。

SQL 的 JOIN 操作基于不同表之间共享的外键，支持超越单表操作的更复杂更新。对于开发人员和数据库管理员而言，带 JOIN 的 UPDATE 能提升效率并降低数据不一致的风险。 

在开始之前，建议先学习 DataCamp 的 Introduction to SQL Server 课程，熟悉 SQL Server 数据库的运作方式。本教程专为 SQL Server 编写。另外，您也可查看我们的教程 Introduction to SQL Joins，学习在多表场景下如何使用 JOIN。

如何使用带 JOIN 的 SQL UPDATE

在 SQL Server 中，带 JOIN 的 UPDATE 可根据另一张表中的匹配数据来更新某张表的记录。此技巧在跨多表同步数据时尤为实用。

将 SQL UPDATE 与 INNER JOIN 一起使用

INNER JOIN 会检索两张表中具有匹配数据的行/记录。与 UPDATE 语句结合使用时，INNER JOIN 可确保仅更新目标表中在连接表里有对应匹配的记录。

带 INNER JOIN 的 UPDATE 的典型结构是：指定需要更新的目标表并定义要更新的列；同时通过 INNER JOIN 按公共列与另一张表关联，以便仅更新所需记录。

INNER JOIN 示例

假设您管理一个零售数据库，需要让 Sales 表与 Customer 表中的变更保持同步。某位客户最近更改了其首选联系方法，您希望这项变更在两张表中得到反映。您将使用带 INNER JOIN 的 UPDATE，确保只修改那些联系信息已更新的客户相关销售记录，而不影响数据中的其他行。

-- Update the Sales table based on the new preferred contact methods in the Customers table
UPDATE s
SET s.ContactMethod = c.PreferredContactMethod
FROM Sales s
INNER JOIN Customers c ON s.CustomerID = c.CustomerID
WHERE c.CustomerID IN (3, 7, 9);技术支持

结合 INNER JOIN 的 UPDATE 最佳实践

在使用 UPDATE 搭配 INNER JOIN 时，请参考以下最佳实践以提高查询效率。

• 为连接列创建索引： 确保在 JOIN 条件中使用的列已建立索引，以优化性能并加速查询执行。

• 限制更新范围： 使用 WHERE 子句，确保只更新相关行，从而提升查询性能。

将 SQL UPDATE 与 LEFT JOIN 一起使用

LEFT JOIN 会检索左（第一）张表的所有记录以及右（第二）张表中匹配的记录。若右表无匹配行，LEFT JOIN 的查询会返回右表列的 NULL。 与 UPDATE 语句结合使用时，即便第二张表没有匹配数据，LEFT JOIN 也能进行更新，这在填补缺失数据时很有用。如果您想复习 SQL 联接，欢迎查看我们的SQL Joins 速查表。

LEFT JOIN 示例

设想您在管理一家在线零售商店的数据库。Customers 表包含所有客户的列表，Orders 表则包含其中部分客户的订单详情。您将使用 LEFT JOIN 来检索并更新所有客户的订单信息，包括那些尚未下单的客户。对于没有下过订单的客户，可将缺失数据填为 NULL 或指定的默认值。

我们的 LEFT JOIN 示例会略有不同，因为当右表没有匹配记录时，LEFT JOIN 会返回 NULL 值。因此，了解如何处理这些 NULL 值很重要。 您可以通过在待更新的列中设置默认值来处理 NULL。借助 COALESCE() 函数，LEFT JOIN 可以为缺失数据设定一个默认值。下面的查询展示了如何为 NULL 设定默认值。

-- Update all customers to set default values where OrderStatus and OrderDate are NULL
UPDATE c
SET 
    c.OrderStatus = COALESCE(o.OrderStatus, 'No Orders'),
    c.OrderDate = COALESCE(o.OrderDate, '2023-01-01')   
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;
-- Select from the updated Customers table
SELECT * 
FROM Customers;技术支持

您还可以在更新列之前，使用 COALESCE() 或 IFNULL() 来处理涉及计算的 NULL 值。请参阅我们的 COALESCE() SQL Function 教程了解更多。

结合 LEFT JOIN 的 UPDATE 最佳实践

在使用 UPDATE 搭配 LEFT JOIN 时，请参考以下最佳实践以提高查询效率。

• 显式指定列： 避免使用 SELECT *，明确指定要更新的列，使查询更清晰并避免意外更新。

• 为连接列创建索引： 确保在 JOIN 条件中使用的列已建立索引，以提升连接操作的性能并缩短整体查询时间。

带 Join 的 SQL Update 的性能考量

理解在 SQL 中使用 UPDATE 搭配 JOIN 的性能影响，对于高效运行查询非常重要。其性能会受到多种因素影响，包括所用表的规模、连接类型以及是否建立了合适的索引。

索引与查询速度

对参与连接条件的列进行索引对于加速更新过程至关重要。适当索引后，连接过程中只需定位匹配行，从而减少表扫描所需时间。

大型数据集的影响

在处理大型数据集时，由于操作资源开销大，查询执行可能会变慢。请参考以下最佳实践以缓解大型数据集带来的性能问题。此外，如果您的工作中需要经常设计或维护数据库，建议尝试 SQL Server for Database Administrators 技能路径。 

• 批量更新： 将更新拆分为更小的批次，降低数据库负载并高效更新记录。

• 限制更新范围： 使用 WHERE 子句明确指定要更新的行，减少查询的处理时间。

常见错误及规避方法

在 SQL Server 中使用 UPDATE 搭配 JOIN 时，您可能会遇到一些常见错误，这些错误可能导致性能问题或误更新数据。下面列出部分错误以及规避方法。

意外的更新

如果未正确指定连接条件，带 JOIN 的 UPDATE 可能会更新超出预期的记录，甚至将整张表全部更新。为避免此错误，请始终使用精确的 JOIN 条件，仅更新所需的行。

笛卡尔积

当连接条件不正确或缺失时，就会产生笛卡尔积，导致查询将左表的每一行与右表的每一行进行匹配。这不仅会拖慢查询操作，还可能产生不准确的结果。为防止该问题，请确保正确定义 JOIN 条件，并指明用于连接的正确列。

带 JOIN 的 SQL UPDATE 的替代方案

尽管带 JOIN 的 UPDATE 是跨关联表更新记录的强大方法，但也有其他可实现更新的方式。以下是一些选项。

子查询

当您希望基于简单条件进行更新时，子查询是 JOIN 的重要替代方案。使用子查询，您可以避免涉及多个复杂表，只需在子查询中指定条件。

下面的示例展示如何使用子查询，将 Customers 表中的最新 CustomerStatus 更新到 Sales 表。

-- Update the CustomerStatus in the Sales table
UPDATE Sales
-- Set the CustomerStatus in Sales to corresponding value from the Customers table
SET CustomerStatus = (
    -- Fetch CustomerStatus from the Customers table for the matching CustomerID
    SELECT CustomerStatus 
    FROM Customers 
    WHERE Customers.CustomerID = Sales.CustomerID
)
-- Update rows in Sales where a matching CustomerID exists in the Customers table
WHERE EXISTS (
    -- Subquery to check if a matching CustomerID exists in the Customers table
    SELECT 1 
    FROM Customers 
    WHERE Customers.CustomerID = Sales.CustomerID
);技术支持

MERGE 语句

MERGE 是一个多功能的 SQL 命令，可在比较两张表的基础上，在单条查询中执行 UPDATE、INSERT 或 DELETE 操作。当需要在两张表之间进行数据同步、同时处理匹配与不匹配的行时，MERGE 非常有用。

使用 MERGE 的好处在于，它提供了一种简洁的方法可在一条查询中执行多种操作。其语法如下所示。

-- Merge data from the source table into the target table
MERGE INTO target_table AS target
USING source_table AS source
    -- Define the common key between the source and target tables
    ON target.common_column = source.common_column
-- When a match is found in both tables based on the common key
WHEN MATCHED THEN
    -- Update the target table's column with the corresponding value from the source table
    UPDATE SET target.column = source.column
-- When a row exists in the source table but not in the target table
WHEN NOT MATCHED THEN
    -- Insert the new data into the target table
    INSERT (column1, column2) 
    VALUES (source.column1, source.column2)
-- When a row exists in the target table but not in the source table
WHEN NOT MATCHED BY SOURCE THEN
    -- Delete the row from the target table
    DELETE;技术支持

结语

掌握在 SQL Server 中使用带 JOIN 的 UPDATE，对于在关系型数据库中高效进行跨表数据更新至关重要。通过该技术，您可以基于另一张表中的匹配数据更新某张表中的记录，确保关联表之间的数据一致与同步。应用这些技术与最佳实践，将有助于优化数据库操作，实现准确、高效、可靠的数据管理。 

如果您想进一步提升 SQL 技能，建议查看 DataCamp 的 SQL Server Fundamentals 技能路径，增强您的数据分析能力。Writing Functions and Stored Procedures in SQL Server 课程也将帮助您学习在 SQL Server 中高效进行数据处理。若您正在准备 SQL 面试，建议阅读我们的博客文章 20 Top SQL Joins Interview Questions 以助备考。最后，如果您希望在数据工程师岗位中运用 SQL 技能，也可考虑学习我们的 Data Engineer in Python 职业路径，完成后还能获得认证。