UPDATE
功能概述
更新 Lakehouse 表中的记录，将指定的列值修改为新的值。
语法格式
UPDATE target_table
SET column_name1 = new_value1 [ , column_name2 = new_value2 , ... ]
[ WHERE condition ]
[ ORDER BY ... ]
[ LIMIT row_count ]
必选参数
target_table
target_table
：要更新的目标表，支持 schema.table
schema.table
格式。SET column = value
SET column = value
：指定要修改的列和新值，支持表达式、函数、子查询。
可选参数
WHERE condition
WHERE condition
：筛选要更新的行——省略时更新全表所有行，执行前务必确认。ORDER BY
ORDER BY
+ LIMIT
LIMIT
：控制更新顺序和数量，适合分批更新大表，避免长事务。
使用示例
示例1：更新单列
给 Electronics 类别的所有商品涨价 10%。
更新前：
SELECT product_id, name, price, category
FROM doc_test.products
WHERE category = 'Electronics'
ORDER BY product_id;
product_id | name | price | category
------------+--------+---------+-------------
1 | Laptop | 5999.00 | Electronics
2 | Phone | 2999.00 | Electronics
5 | Tablet | 3499.00 | Electronics
执行更新：
UPDATE doc_test.products
SET price = price * 1.1
WHERE category = 'Electronics';
更新后：
SELECT product_id, name, price, category
FROM doc_test.products
WHERE category = 'Electronics'
ORDER BY product_id;
product_id | name | price | category
------------+--------+---------+-------------
1 | Laptop | 6598.90 | Electronics
2 | Phone | 3298.90 | Electronics
5 | Tablet | 3848.90 | Electronics
示例2：同时更新多列
将 id 为 4 的员工薪资加 1000，并将其状态改为在职。
更新前：
SELECT id, name, dept, salary, is_active
FROM doc_test.employees
WHERE id = 4;
id | name | dept | salary | is_active
----+-------+------+---------+-----------
4 | Diana | HR | 7500.00 | false
执行更新：
UPDATE doc_test.employees
SET salary = salary + 1000, is_active = true
WHERE id = 4;
更新后：
SELECT id, name, dept, salary, is_active
FROM doc_test.employees
WHERE id = 4;
id | name | dept | salary | is_active
----+-------+------+---------+-----------
4 | Diana | HR | 8500.00 | true
示例3：使用子查询更新
将所有
pending
pending
状态且商品属于 Electronics 类别的订单标记为
processing
processing
。
更新前：
SELECT order_id, product, amount, status
FROM doc_test.orders
ORDER BY order_id;
order_id | product | amount | status
----------+---------+---------+-----------
1001 | Laptop | 5999.00 | completed
1002 | Phone | 2999.00 | pending
1003 | Tablet | 3499.00 | completed
执行更新：
UPDATE doc_test.orders
SET status = 'processing'
WHERE status = 'pending'
AND product IN (
SELECT name
FROM doc_test.products
WHERE category = 'Electronics'
);
更新后：
SELECT order_id, product, amount, status
FROM doc_test.orders
ORDER BY order_id;
order_id | product | amount | status
----------+---------+---------+------------
1001 | Laptop | 5999.00 | completed
1002 | Phone | 2999.00 | processing
1003 | Tablet | 3499.00 | completed
示例4：ORDER BY + LIMIT 分批更新
对价格最低的 2 件商品各扣减 10 件库存，模拟分批处理。
更新前：
SELECT product_id, name, price, stock
FROM doc_test.products
ORDER BY price;
product_id | name | price | stock
------------+--------+---------+-------
4 | Chair | 499.00 | 80
3 | Desk | 899.00 | 30
2 | Phone | 2999.00 | 120
5 | Tablet | 3499.00 | 60
1 | Laptop | 5999.00 | 50
执行更新：
UPDATE doc_test.products
SET stock = stock - 10
ORDER BY price
LIMIT 2;
更新后：
SELECT product_id, name, price, stock
FROM doc_test.products
ORDER BY price;
product_id | name | price | stock
------------+--------+---------+-------
4 | Chair | 499.00 | 70
3 | Desk | 899.00 | 20
2 | Phone | 2999.00 | 120
5 | Tablet | 3499.00 | 60
1 | Laptop | 5999.00 | 50
注意事项
- 省略
WHERE
WHERE
会更新全表所有行，建议先用 SELECT
SELECT
验证条件再执行 UPDATE
UPDATE
。
ORDER BY
ORDER BY
与 LIMIT
LIMIT
配合使用可控制每批更新的行数，适合大表分批处理，避免长事务。- 对生产环境执行更新前，建议先在测试环境验证 SQL 逻辑。
相关指南
- 数据更新与清理：UPDATE/DELETE 的典型场景、分批更新、子查询条件等使用模式
- SQL DML使用指南：INSERT/UPDATE/DELETE/MERGE 的注意事项与最佳实践
