如何更新具有特定列名称的所有表 [英] how to update all tables with a particular column name

问题描述

我试图更新所有以字符串开头的表，如'agg％'和column_name ='％userid％'...
但是我没有看到任何这样的例子在线，即使我能够找到选项选择具有特定列名和表名的所有表我需要做相同的更新这些表，像这样：

I am trying to update all tables starting with string like 'agg%' and column_name ='%userid%'... But i dont see any such examples online even though i was able to find option to select all tables with a particular column name and table name I need to do the same to update those tables something like this :

    update TABLE_NAME set COLUMN_NAME='rajeev' WHERE COLUMN_NAME LIKE '%userid%'
and TABLE_NAME LIKE 'agg%'
FROM INFORMATION_SCHEMA.COLUMNS;

感谢您的帮助。

。

推荐答案

获取更新查询您的条件

To get the update query for your condition

     select 
        'update '||c.table_name||' set '||c.COLUMN_NAME||' = ''rajeev'';' 
         as my_update_query
     from 
        (select 
            table_name,COLUMN_NAME
         from INFORMATION_SCHEMA.COLUMNS 
         where table_name LIKE 'agg%' and COLUMN_NAME LIKE '%userid%') c

---

执行

 do $$
  declare
    arow record;
  begin
    for arow in
    select 
        'update '||c.table_name||' set '||c.COLUMN_NAME||' = ''rajeev'';' 
         as my_update_query
     from 
        (select 
            table_name,COLUMN_NAME
         from INFORMATION_SCHEMA.COLUMNS 
         where table_name LIKE 'agg%' and COLUMN_NAME LIKE '%userid%') c
    loop
     execute arow.my_update_query;
    end loop;
  end;
$$;

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