pg数据库 多种update方式

场景

常见的几种 update 数据的场景

1. 单表更新（区分单表数据量级、是否带过滤条件）
2. 多表联合更新，用 B 表的数据更新 A 表，有点类似 Excel 的 vlookup 函数。

场景就这两个，可以有多种写法，但目的是一样的。

实现

语法（官方文档）

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

单表更新

这是最常见的更新方式，用于更新满足特定条件的行，也可以结合 case 表达式实现条件更新。

select column1, column2 from table_name where condition;

--更新前养成先查询的习惯
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition; --有条件的更新，如果没有 where 过滤条件，就是全表更新，全表更新需要谨慎。

--条件更新（结合 CASE 表达式）根据不同条件更新不同的值。
UPDATE table_name
SET column1 = CASE
    WHEN id = 1 THEN 'value1'
    WHEN id = 2 THEN 'value2'
    ELSE column1
END
WHERE id IN (1, 2, 3);

多表联合更新

1. 使用子查询
2. 使用 from 子句
3. 使用 CTE 表达式（3 是在 2 的基础上的变体）

--基于子查询的结果更新数据
UPDATE table1
SET column1 = (SELECT column2 FROM table2 WHERE table2.id = table1.id)
WHERE condition;

--使用 FROM 子句，可以从B表中获取数据来更新目标A表。
UPDATE table1
SET column1 = table2.column2
FROM table2
WHERE table1.id = table2.id AND condition;

--在 FROM 子句的基础上，把 FROM 的表改为 with 子句
WITH updated_rows AS (
    SELECT id, new_value
    FROM some_calculation
)
UPDATE table_name
SET column1 = updated_rows.new_value
FROM updated_rows
WHERE table_name.id = updated_rows.id;

更新返回

• 更新完成后，默认会返回更新的行数量。
• 如果增加了returning子句，则会返回所有更新的行。可以知道哪些行被更新了。
• 更新返回结合 CTE 可以实现更新后的数据查询和插入

--更新返回returning
update table_name
set col = 1
where col is null
returning col as xx, col_0 as id, col_1 as yy


--使用returning，做查询插入。将更新后的记录插入另一张表
insert into another_table(xx, id, yy)
with updated as (
    update table_name
    set col = 1
    where col is null
    returning col as xx, col_0 as id, col_1 as yy
)
select * from updated

经验总结

在使用 UPDATE 时，一些经验总结

• 在执行大规模更新前，先在小范围数据上测试。每次更新前都要先查询看一下或者备份；
• 大批量的数据，确保有适当的索引以提高性能（在 where 条件中使用索引列），另外尽量切分为小批量数据分批执行，减少锁竞争；
• 表要定期执行 vaccum和analyze，做表定期维护；
• 尽量避免频繁更新，可以从业务表设计上规避频繁更新情况；
• 注意并发更新可能带来的问题，如死锁；
• 如果有相互依赖的表更新操作，最好把他们放到一个事务里面提交，以保证数据一致性；