数据更新 #
一、UPDATE基础 #
1.1 基本语法 #
sql
-- 基本更新语法
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
-- 示例表
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100) NOT NULL,
email VARCHAR(100),
age INT,
status VARCHAR(20) DEFAULT 'active',
updated_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO users (name, email, age) VALUES
('Alice', 'alice@example.com', 25),
('Bob', 'bob@example.com', 30),
('Carol', 'carol@example.com', 28);
-- 更新单行
UPDATE users
SET age = 26
WHERE name = 'Alice';
-- 查看结果
SELECT * FROM users WHERE name = 'Alice';
1.2 更新多个列 #
sql
-- 更新多个列
UPDATE users
SET
email = 'alice.new@example.com',
age = 27,
status = 'verified'
WHERE name = 'Alice';
-- 使用表达式更新
UPDATE users
SET
age = age + 1,
updated_at = NOW()
WHERE name = 'Alice';
1.3 更新所有行 #
sql
-- 更新所有行 (慎用!)
UPDATE users
SET status = 'inactive';
-- 通常应该加 WHERE 条件
UPDATE users
SET status = 'inactive'
WHERE age > 50;
二、条件更新 #
2.1 使用WHERE条件 #
sql
-- 等值条件
UPDATE users
SET status = 'verified'
WHERE email = 'alice@example.com';
-- 范围条件
UPDATE users
SET status = 'senior'
WHERE age >= 30;
-- 多条件组合
UPDATE users
SET status = 'premium'
WHERE age > 25 AND email LIKE '%@company.com';
-- IN 条件
UPDATE users
SET status = 'vip'
WHERE name IN ('Alice', 'Bob', 'Carol');
-- 子查询条件
UPDATE users
SET status = 'active_buyer'
WHERE id IN (
SELECT DISTINCT user_id
FROM orders
WHERE created_at > NOW() - INTERVAL '30 days'
);
2.2 使用CASE表达式 #
sql
-- 使用 CASE 条件更新
UPDATE users
SET status = CASE
WHEN age < 25 THEN 'junior'
WHEN age >= 25 AND age < 35 THEN 'mid-level'
WHEN age >= 35 THEN 'senior'
ELSE 'unknown'
END;
-- 带条件的 CASE 更新
UPDATE users
SET
status = CASE
WHEN age >= 30 THEN 'senior'
ELSE status -- 保持原值
END,
updated_at = NOW()
WHERE email IS NOT NULL;
三、批量更新 #
3.1 批量更新策略 #
text
批量更新策略
┌─────────────────────────────────────────────────────────────┐
│ │
│ 1. 分批更新 │
│ ├── 每次更新一定数量 │
│ ├── 减少锁持有时间 │
│ └── 降低冲突概率 │
│ │
│ 2. 使用事务 │
│ ├── 保证原子性 │
│ └── 可回滚 │
│ │
│ 3. 避免热点 │
│ ├── 分散更新 │
│ └── 使用队列 │
│ │
│ 4. 使用 CTE │
│ ├── 复杂更新逻辑 │
│ └── 提高可读性 │
│ │
└─────────────────────────────────────────────────────────────┘
3.2 分批更新示例 #
sql
-- 分批更新大量数据
-- 每次更新 1000 行
-- 方法1: 使用 LIMIT
UPDATE users
SET status = 'processed'
WHERE status = 'pending'
LIMIT 1000;
-- 方法2: 使用 ID 范围
UPDATE users
SET status = 'processed'
WHERE id >= 'uuid-start' AND id < 'uuid-end'
AND status = 'pending';
-- 方法3: 使用事务
BEGIN;
UPDATE users
SET status = 'processed'
WHERE status = 'pending'
LIMIT 1000;
COMMIT;
3.3 使用CTE更新 #
sql
-- 使用 CTE 进行复杂更新
WITH active_users AS (
SELECT user_id, COUNT(*) as order_count
FROM orders
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY user_id
HAVING COUNT(*) > 5
)
UPDATE users u
SET status = 'active_buyer'
FROM active_users a
WHERE u.id = a.user_id;
-- 使用 CTE 更新并返回结果
WITH updated AS (
UPDATE users
SET status = 'verified'
WHERE email LIKE '%@company.com'
RETURNING *
)
SELECT * FROM updated;
四、多表更新 #
4.1 使用FROM子句 #
sql
-- 从其他表获取数据更新
UPDATE users u
SET
total_orders = o.order_count,
total_amount = o.total_amount
FROM (
SELECT
user_id,
COUNT(*) as order_count,
SUM(amount) as total_amount
FROM orders
GROUP BY user_id
) o
WHERE u.id = o.user_id;
-- 使用 JOIN 更新
UPDATE users u
SET status = 'premium'
FROM orders o
WHERE u.id = o.user_id
AND o.amount > 10000
AND o.created_at > NOW() - INTERVAL '1 year';
4.2 使用子查询 #
sql
-- 使用子查询更新
UPDATE users
SET status = 'vip'
WHERE id IN (
SELECT user_id
FROM orders
WHERE amount > 10000
GROUP BY user_id
HAVING SUM(amount) > 50000
);
-- 使用相关子查询
UPDATE users u
SET last_order_date = (
SELECT MAX(created_at)
FROM orders o
WHERE o.user_id = u.id
);
五、RETURNING子句 #
5.1 返回更新结果 #
sql
-- 返回更新的行
UPDATE users
SET age = age + 1
WHERE name = 'Alice'
RETURNING *;
-- 返回特定列
UPDATE users
SET status = 'verified'
WHERE email = 'alice@example.com'
RETURNING id, name, status;
-- 返回计算结果
UPDATE users
SET
age = age + 1,
updated_at = NOW()
WHERE name = 'Alice'
RETURNING
id,
name,
age,
age - 1 AS previous_age,
updated_at;
5.2 使用返回结果 #
sql
-- 将更新结果插入到另一个表
WITH updated AS (
UPDATE users
SET status = 'verified'
WHERE email LIKE '%@company.com'
RETURNING id, name, status
)
INSERT INTO user_audit (user_id, action, old_status, new_status)
SELECT id, 'status_update', 'pending', status
FROM updated;
-- 更新并统计
WITH updated AS (
UPDATE users
SET status = 'processed'
WHERE status = 'pending'
RETURNING *
)
SELECT
COUNT(*) as updated_count,
NOW() as update_time
FROM updated;
六、高级更新 #
6.1 使用JSON更新 #
sql
-- 创建带JSON列的表
CREATE TABLE products (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100),
attributes JSONB
);
INSERT INTO products (name, attributes) VALUES
('Product A', '{"color": "red", "size": "M", "price": 100}');
-- 更新JSON字段
UPDATE products
SET attributes = jsonb_set(attributes, '{price}', '150')
WHERE name = 'Product A';
-- 添加JSON字段
UPDATE products
SET attributes = attributes || '{"brand": "BrandX"}'
WHERE name = 'Product A';
-- 删除JSON字段
UPDATE products
SET attributes = attributes - 'brand'
WHERE name = 'Product A';
-- 更新嵌套JSON
UPDATE products
SET attributes = jsonb_set(
attributes,
'{specs,weight}',
'500'
)
WHERE name = 'Product A';
6.2 使用数组更新 #
sql
-- 创建带数组列的表
CREATE TABLE tags (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100),
tags STRING[]
);
INSERT INTO tags (name, tags) VALUES
('Item A', ARRAY['tag1', 'tag2']);
-- 添加数组元素
UPDATE tags
SET tags = array_append(tags, 'tag3')
WHERE name = 'Item A';
-- 删除数组元素
UPDATE tags
SET tags = array_remove(tags, 'tag1')
WHERE name = 'Item A';
-- 替换数组
UPDATE tags
SET tags = ARRAY['new1', 'new2', 'new3']
WHERE name = 'Item A';
七、更新性能 #
7.1 性能建议 #
text
更新性能优化建议
┌─────────────────────────────────────────────────────────────┐
│ │
│ 1. 使用索引 │
│ ├── WHERE 条件使用索引列 │
│ └── 加快定位速度 │
│ │
│ 2. 批量更新 │
│ ├── 减少事务次数 │
│ └── 提高吞吐量 │
│ │
│ 3. 避免热点 │
│ ├── 分散更新 │
│ └── 避免频繁更新同一行 │
│ │
│ 4. 合理使用事务 │
│ ├── 短事务 │
│ └── 减少锁持有时间 │
│ │
│ 5. 使用 RETURNING │
│ ├── 减少额外查询 │
│ └── 提高效率 │
│ │
└─────────────────────────────────────────────────────────────┘
7.2 查看执行计划 #
sql
-- 查看更新执行计划
EXPLAIN UPDATE users
SET status = 'verified'
WHERE email = 'alice@example.com';
-- 查看详细执行计划
EXPLAIN ANALYZE UPDATE users
SET status = 'verified'
WHERE email = 'alice@example.com';
八、常见问题 #
8.1 更新冲突 #
sql
-- 问题: 并发更新冲突
-- 解决: 使用事务和重试
DO $$
BEGIN
DECLARE
retry_count INT DEFAULT 0;
BEGIN
WHILE retry_count < 3 LOOP
BEGIN
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
COMMIT;
EXIT;
EXCEPTION WHEN serialization_failure THEN
retry_count := retry_count + 1;
ROLLBACK;
END;
END LOOP;
END;
END;
$$;
8.2 更新不存在的行 #
sql
-- 问题: 更新不存在的行，没有报错也没有更新
UPDATE users SET status = 'verified' WHERE id = 'non-existent-uuid';
-- Query OK, 0 rows affected
-- 解决: 检查更新行数
UPDATE users
SET status = 'verified'
WHERE id = 'non-existent-uuid'
RETURNING id;
-- 或使用 RETURNING 检查
DO $$
DECLARE
updated_id UUID;
BEGIN
UPDATE users
SET status = 'verified'
WHERE id = 'non-existent-uuid'
RETURNING id INTO updated_id;
IF updated_id IS NULL THEN
RAISE EXCEPTION 'User not found';
END IF;
END;
$$;
8.3 大批量更新 #
sql
-- 问题: 大批量更新导致性能问题
-- 解决: 分批更新
-- 方法1: 分批处理
DO $$
DECLARE
batch_size INT := 1000;
total_updated INT := 0;
batch_updated INT;
BEGIN
LOOP
UPDATE users
SET status = 'processed'
WHERE status = 'pending'
LIMIT batch_size;
GET DIAGNOSTICS batch_updated = ROW_COUNT;
total_updated := total_updated + batch_updated;
EXIT WHEN batch_updated = 0;
COMMIT;
END LOOP;
RAISE NOTICE 'Total updated: %', total_updated;
END;
$$;
九、总结 #
UPDATE 语句要点：
| 类型 | 说明 |
|---|---|
| 基本更新 | 单列、多列、条件更新 |
| 批量更新 | 分批、事务、CTE |
| 多表更新 | FROM子句、子查询 |
| RETURNING | 返回更新结果 |
| JSON更新 | JSONB操作 |
下一步，让我们学习数据删除操作！
最后更新：2026-03-27