当前位置：首页 > ds >正文

SQL 使用 UPDATE FROM 语法进行更新

ds 2025/7/16 22:04:08

UPDATE FROM 是一种常见的 SQL 语法模式，允许你基于其他表的数据来更新目标表。这种语法在不同数据库系统中有所不同，下面我将介绍几种主要数据库的实现方式。

PostgreSQL/SQL Server 语法

UPDATE target_table
SET target_column = source_table.source_column
FROM source_table
WHERE target_table.join_key = source_table.join_key
AND source_table.condition = true;

示例：

-- 更新员工工资为对应部门平均工资的110%
UPDATE employees e
SET salary = d.avg_salary * 1.10
FROM (SELECT department_id, AVG(salary) as avg_salary FROM employees GROUP BY department_id
) d
WHERE e.department_id = d.department_id;

MySQL 语法

UPDATE target_table t
JOIN source_table s ON t.join_key = s.join_key
SET t.target_column = s.source_column
WHERE s.condition = true;

示例：

-- 更新产品价格为供应商建议价格的95%
UPDATE products p
JOIN suppliers s ON p.supplier_id = s.supplier_id
SET p.price = s.suggested_price * 0.95
WHERE s.is_preferred = 1;

Oracle 语法

UPDATE (SELECT t.target_column, s.source_columnFROM target_table t, source_table sWHERE t.join_key = s.join_keyAND s.condition = true
)
SET target_column = source_column;

示例：

-- 更新客户等级基于最近订单金额
UPDATE (SELECT c.customer_level, o.order_totalFROM customers c, orders oWHERE c.customer_id = o.customer_idAND o.order_date > ADD_MONTHS(SYSDATE, -3)
)
SET customer_level = CASE WHEN order_total > 1000 THEN 'GOLD'WHEN order_total > 500 THEN 'SILVER'ELSE 'STANDARD'END;

SQLite 语法

UPDATE target_table
SET target_column = (SELECT source_column FROM source_table WHERE join_condition
)
WHERE exists_condition;

示例：

-- 更新库存数量为最新进货记录的数量
UPDATE inventory
SET quantity = (SELECT quantity FROM latest_deliveriesWHERE inventory.product_id = latest_deliveries.product_id
)
WHERE EXISTS (SELECT 1 FROM latest_deliveriesWHERE inventory.product_id = latest_deliveries.product_id
);