Lakehouse 数据更新与清理指南
概述
在数据仓库运维中，经常需要修正错误数据、清理过期记录或重置表数据。云器 Lakehouse 提供
UPDATE
UPDATE
、
DELETE
DELETE
和
TRUNCATE
TRUNCATE
三种数据修改方式，分别适用于不同的场景。本指南按业务场景分类，帮助你快速掌握安全高效的数据更新与清理方法。
涉及的 SQL 命令
| 命令 | 用途 | 适用场景 |
|---|
UPDATE ... SET ... WHERE
UPDATE ... SET ... WHERE | 按条件更新行 | 修正错误数据、状态变更 |
UPDATE ... ORDER BY ... LIMIT
UPDATE ... ORDER BY ... LIMIT | 分批更新 | 大数据量安全更新 |
DELETE FROM ... WHERE
DELETE FROM ... WHERE | 按条件删除行 | 清理过期或无效数据 |
TRUNCATE TABLE
TRUNCATE TABLE | 清空全表 | 快速重置表数据 |
前置准备
以下示例使用模拟的员工表
employees_update
employees_update
：
-- 创建测试表
CREATE TABLE IF NOT EXISTS employees_update (
emp_id INT,
emp_name STRING,
dept STRING,
salary DOUBLE,
status STRING
);
-- 插入测试数据
INSERT INTO employees_update VALUES
(1, 'Alice', 'Engineering', 12000, 'active'),
(2, 'Bob', 'Engineering', 9500, 'active'),
(3, 'Carol', 'Marketing', 8500, 'active'),
(4, 'David', 'Marketing', 7800, 'inactive'),
(5, 'Eve', 'HR', 6000, 'active');
条件更新数据
使用
UPDATE
UPDATE
语句修改满足条件的行。务必使用
WHERE
WHERE
子句限制更新范围。
-- 给 Engineering 部门员工涨薪 10%
UPDATE employees_update
SET salary = salary * 1.1
WHERE dept = 'Engineering';
结果验证：
SELECT emp_id, emp_name, dept, salary FROM employees_update ORDER BY emp_id;
| emp_id | emp_name | dept | salary |
|---|
| 1 | Alice | Engineering | 13200 |
| 2 | Bob | Engineering | 10450 |
| 3 | Carol | Marketing | 8500 |
| 4 | David | Marketing | 7800 |
| 5 | Eve | HR | 6000 |
⚠️ 注意：DOUBLE 类型计算可能存在浮点精度问题（如 13200.000000000002），建议使用
ROUND(salary, 2)
ROUND(salary, 2)
格式化。不带
WHERE
WHERE
的
UPDATE
UPDATE
会更新全表所有行，请谨慎使用。
批量更新
当需要更新的行数极大时，使用
ORDER BY + LIMIT
ORDER BY + LIMIT
分批更新，避免长时间锁定。
-- 每次更新 2 行，按 emp_id 排序保证顺序
UPDATE employees_update
SET status = 'reviewed'
WHERE status = 'active'
ORDER BY emp_id
LIMIT 2;
结果验证：
| emp_id | emp_name | status |
|---|
| 1 | Alice | reviewed |
| 2 | Bob | reviewed |
| 3 | Carol | active |
| 5 | Eve | active |
💡 提示：在循环中重复执行此语句，直到
affected rows = 0
affected rows = 0
，即可完成全量分批更新。
条件删除数据
使用
DELETE
DELETE
语句删除满足条件的行。同样建议使用
WHERE
WHERE
子句限制删除范围。
-- 删除状态为 inactive 的员工
DELETE FROM employees_update
WHERE status = 'inactive';
结果验证：
SELECT * FROM employees_update ORDER BY emp_id;
| emp_id | emp_name | dept | salary | status |
|---|
| 1 | Alice | Engineering | 13200 | reviewed |
| 2 | Bob | Engineering | 10450 | reviewed |
| 3 | Carol | Marketing | 8500 | active |
| 5 | Eve | HR | 6000 | active |
清空表数据
使用
TRUNCATE TABLE
TRUNCATE TABLE
快速清空整表数据，保留表结构。比
DELETE
DELETE
更高效。
-- 清空表数据
TRUNCATE TABLE employees_update;
结果验证：
SELECT COUNT(*) FROM employees_update;
⚠️ 注意：> *
TRUNCATE
TRUNCATE
不可回滚（不在 Time Travel 保留范围内），请谨慎使用。
TRUNCATE
TRUNCATE
不支持 WHERE
WHERE
条件，只能清空全表。- 如需条件清空，请使用
DELETE
DELETE
。
基于关联表的更新
当需要根据另一张表的数据更新当前表时，可以使用子查询或
MERGE INTO
MERGE INTO
。
-- 重新插入数据用于演示
INSERT INTO employees_update VALUES
(1, 'Alice', 'Engineering', 13200, 'reviewed'),
(2, 'Bob', 'Engineering', 10450, 'reviewed');
-- 创建薪资调整表
CREATE TABLE IF NOT EXISTS salary_adjustments (
emp_id INT,
adjust_amount DOUBLE
);
INSERT INTO salary_adjustments VALUES
(1, 500),
(2, 300);
-- 使用子查询更新薪资
UPDATE employees_update
SET salary = salary + (
SELECT adjust_amount
FROM salary_adjustments
WHERE salary_adjustments.emp_id = employees_update.emp_id
)
WHERE emp_id IN (SELECT emp_id FROM salary_adjustments);
结果验证：
| emp_id | emp_name | salary |
|---|
| 1 | Alice | 13700 |
| 2 | Bob | 10750 |
💡 提示：对于复杂的关联更新，推荐使用
MERGE INTO
MERGE INTO
，语法更清晰。
清理测试数据
完成更新与清理验证后，建议清理测试表：
-- 删除测试表
DROP TABLE IF EXISTS employees_update;
DROP TABLE IF EXISTS salary_adjustments;
💡 提示：Lakehouse 支持
UNDROP TABLE
UNDROP TABLE
，误删后可在保留期内恢复。
注意事项
- WHERE 子句：
UPDATE
UPDATE
和 DELETE
DELETE
强烈建议使用 WHERE
WHERE
子句，避免误操作全表。
- 事务性：单次
UPDATE
UPDATE
或 DELETE
DELETE
是原子操作，要么全部成功，要么全部失败。
- TRUNCATE 不可恢复：
TRUNCATE
TRUNCATE
操作不会保留历史版本，无法通过 Time Travel 恢复。
- 动态表限制：Dynamic Table 不支持直接
UPDATE
UPDATE
或 DELETE
DELETE
，数据由上游表变更触发自动刷新。
- 分批操作：大数据量更新/删除时，使用
ORDER BY + LIMIT
ORDER BY + LIMIT
分批执行，避免长时间占用资源。
相关文档
