更新多个表中的列 [英] Update column in multiple tables

问题描述

假设我在一个模式的多个表中有一个名为partner的列:

Let's say I have a column called partner in multiple tables within one schema:

select table_name from information_schema.columns where column_name = 'partner';

如何将值partner = 100更改为partner = 101的所有列?

How would I update all columns where value partner = 100 to partner = 101?

推荐答案

对于一次操作，执行动态SQL的DO语句应该可以正常工作:

For a one-time operation, a DO statement executing dynamic SQL should serve just fine:

DO
$do$
DECLARE
   _tbl text;
BEGIN
FOR _tbl  IN
    SELECT quote_ident(table_name)  -- escape identifier!
    FROM   information_schema.columns
    WHERE  table_schema = 'public'  -- your schema (!!)
    AND    column_name = 'partner'  -- your column name
LOOP
   RAISE NOTICE '%',
-- EXECUTE
  'UPDATE ' || _tbl || ' SET partner = 101 WHERE partner = 100';
END LOOP;
END
$do$

在注释RAISE并取消注释EXECUTE之前，请检查生成的代码.

Inspect the generated code before you comment RAISE and uncomment the EXECUTE.

这是此相关答案中功能更广泛的功能的简化版本，带有更多说明:

This is a largely simplified version of the more versatile function in this related answer with more explanation:

• 信息架构还是系统目录?

  Information schema or system catalog?

  • 如何检查如果表存在于给定的模式中

  这篇关于更新多个表中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！