SQL - update multiple columns to null based on some where clause-CodePudding

I am trying to construct a SQL statement that performs the following:

For a specific row of a specific table, for all columns where a value = '{}" , make the value NULL

For example:

table XYZ:

"col_id"    "col2"  "col3"  "col4"  "col5"

1234        {}       {}     PDQ      ABC
5678        {DO}     {RE}   DEF      HIJ
5678        {MI}     {}   ABC      PDQ

If I want: for table XYZ, where col_id = 1234, make all columns with value {} null,

the result would be:

"col_id"    "col2"  "col3"  "col4"  "col5"

1234        NULL     NULL     PDQ      ABC
5678        {DO}     {RE}   DEF      HIJ
5678        {MI}     {}   ABC      PDQ

Grateful for any assistance. Thank you.

CodePudding user response：

Use NULLIF:

UPDATE XYZ set col2 = NULLIF(col2, '{}') ... where customer_id = 1234`

Any existing value that is = '{}' will be converted to NULL. UPDATE needs the columns that are updated to be specified so you cannot get out of naming them.

CodePudding user response：

For a variable set of columns you should use dynamic query by a function like

create or replace function update_columns(id int) returns varchar as
$$
DECLARE
    column_name text;
    set_clause text;
    r_count int;    
    query text;
BEGIN
    FOR column_name IN select i.column_name  FROM information_schema.columns i 
    WHERE table_schema = 'public' and i.column_name !='id' AND table_name   = 'xyz' LOOP
        set_clause:= concat(set_clause, column_name||'=NULLIF('||column_name||', ''{}''), ');
    END LOOP;
    set_clause:=left(set_clause,-2);
    query:= 'UPDATE xyz set '|| set_clause||' where id='||id::varchar;
    RAISE NOTICE '%', query;        
    execute query;
    GET DIAGNOSTICS r_count = ROW_COUNT;    
    return 'Rows affected ='||r_count;
END;
$$ language plpgsql;

Then execute

select update_columns(1234);