更新以特定字符串开头的多个列 [英] Update multiple columns that start with a specific string

问题描述

我正尝试更新数据库中的一堆列，以测试功能.我有一个使用休眠模式构建的表，因此为嵌入式实体创建的所有列均以相同的名称开头. IE. contact_info_address_street1，contact_info_address_street2等

I am trying to update a bunch of columns in a DB for testing purposes of a feature. I have a table that is built with hibernate so all of the columns that are created for an embedded entity begin with the same name. I.e. contact_info_address_street1, contact_info_address_street2, etc.

我正在尝试找出是否有办法对以下事情产生影响:

I am trying to figure out if there is a way to do something to the affect of:

UPDATE table SET contact_info_address_* = null;

如果没有，我知道我可以做很长的路要走，只是寻找一种方法来将来帮助自己，如果我需要为另一组不同的列重新做一遍.

If not, I know I can do it the long way, just looking for a way to help myself out in the future if I need to do this all over again for a different set of columns.

推荐答案

没有方便的快捷方式.如果您必须做很多这样的事情，则可以创建一个函数来动态执行sql并实现您的目标.

There's no handy shortcut sorry. If you have to do this kind of thing a lot, you could create a function to dynamically execute sql and achieve your goal.

CREATE OR REPLACE FUNCTION reset_cols() RETURNS boolean AS $$ BEGIN 
    EXECUTE (select 'UPDATE table SET ' 
                  || array_to_string(array(
                              select column_name::text 
                              from information_schema.columns 
                              where table_name = 'table' 
                              and column_name::text like 'contact_info_address_%'
                     ),' = NULL,') 
                  || ' = NULL'); 
    RETURN true; 
 END; $$ LANGUAGE plpgsql;

-- run the function
SELECT reset_cols();

虽然不是很好.更好的函数是将表名和列前缀作为args接受的函数.我将留给读者练习:)

It's not very nice though. A better function would be one that accepts the tablename and column prefix as args. Which I'll leave as an exercise for the readers :)

这篇关于更新以特定字符串开头的多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！