sql-update-joins

SQL - 使用 UPDATE JOIN 跨表更新数据

在关系型数据库中，数据通常被规范化并分散在多个表中。虽然标准的 UPDATE 语句只修改单个表，但实际场景中经常需要根据一个表的值来更新另一个表。这时，强大但特定于数据库的 UPDATE ... JOIN（或等效）语法就变得至关重要。

例如，在电商系统中，当客户订单完成后，你可能需要更新 orders 表将状态设置为“已发货”，并同时减少 products 表中的 stock_quantity（库存数量）。原子地执行这些相关更新对于数据完整性至关重要。

跨表更新的概念

跨表更新将 UPDATE 语句与 JOIN 子句结合起来。这允许你定义目标表（正在更新的表）和源表（提供新值或条件的表）之间的关系。JOIN 子句识别目标表中哪些行与源表中的特定行对应，然后 SET 子句执行更新。

重要提示：使用 JOIN 执行更新的语法不是 ANSI SQL 标准的一部分，并且在 PostgreSQL、MySQL 和 SQL Server 等不同数据库系统之间差异很大。务必为你特定的数据库使用正确的语法。

不同数据库系统的语法

让我们回顾一下最常见数据库管理系统的语法。

PostgreSQL 语法

UPDATE target_table
SET column1 = source_table.new_value1
FROM source_table
WHERE target_table.join_column = source_table.join_column;

MySQL 语法

UPDATE target_table
JOIN source_table ON target_table.join_column = source_table.join_column
SET target_table.column1 = source_table.new_value1;

SQL Server 语法

UPDATE target_table
SET column1 = source_table.new_value1
FROM target_table
JOIN source_table ON target_table.join_column = source_table.join_column;

实际示例：更新产品库存

让我们考虑一个真实的电商场景。我们有一个 products 表，包含当前库存信息；以及一个 sales 表，记录新的销售数据。我们需要在销售发生后减少 products 表中的库存数量。

首先，让我们创建并填充表。请注意使用了 INT 和 TIMESTAMP 等适当的数据类型。

-- 用于存储产品信息和库存的表
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    stock_quantity INT NOT NULL CHECK (stock_quantity >= 0)
);

-- 用于记录销售交易的表
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT NOT NULL,
    quantity_sold INT NOT NULL,
    sale_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 插入初始库存
INSERT INTO products (product_id, product_name, stock_quantity) VALUES
(101, 'Wireless Mouse', 50),
(102, 'USB-C Cable', 120);

-- 记录新的销售
INSERT INTO sales (sale_id, product_id, quantity_sold) VALUES
(1, 101, 5);

现在，让我们编写一个查询，根据新的销售更新 products 表。本示例将使用 PostgreSQL 语法。

-- 使用 PostgreSQL 语法更新查询
UPDATE products
SET stock_quantity = products.stock_quantity - sales.quantity_sold
FROM sales
WHERE products.product_id = sales.product_id AND sales.sale_id = 1;

验证

要确认更新是否成功，你可以查询 products 表。

SELECT * FROM products WHERE product_id = 101;

结果将显示“无线鼠标”的库存已减少。

产品ID	产品名称	库存数量
101	Wireless Mouse	45

最佳实践和常见陷阱

• 使用事务：当执行必须同时成功或失败的更新（例如更新订单和库存）时，务必将语句封装在事务中（BEGIN; ... COMMIT; 或 ROLLBACK;）。这确保了数据的原子性。
• 使用 SELECT 进行测试：在运行破坏性 UPDATE 查询之前，编写一个具有完全相同 JOIN 和 WHERE 子句的 SELECT 语句。这允许你预览将受影响的行，而无需更改任何数据。
• 明确使用别名：在复杂的 JOIN 中，使用表别名（例如，UPDATE p SET ... FROM products AS p JOIN ...）可以提高可读性并防止因列名歧义而导致的错误。
• 索引 JOIN 列：确保用于 JOIN 条件的列（我们示例中的 product_id）已建立索引。这可以显著提高大型表上更新操作的性能。

深入学习：使用 CTE 进行复杂更新

对于更复杂的场景，例如需要通过多个步骤计算新值时，公共表表达式（Common Table Expressions, CTE）可以使你的 UPDATE 语句更具可读性和可维护性。你可以在 CTE 中执行复杂的聚合或转换，然后在最终的 UPDATE 中与其进行连接。

-- 在 PostgreSQL 中使用 CTE 的概念示例
WITH aggregated_sales AS (
    SELECT
        product_id,
        SUM(quantity_sold) AS total_sold
    FROM recent_sales_batch
    GROUP BY product_id
)
UPDATE products p
SET stock_quantity = p.stock_quantity - ag.total_sold
FROM aggregated_sales ag
WHERE p.product_id = ag.product_id;