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

Question

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.

set col2 = case col2 when '{}' then null else col2 end, col3 = case col3 when '{}' then null else col3 end,... — Bryan Dellinger
– Bryan Dellinger, Commented Jul 21, 2022 at 16:54
@BryanDellinger thanks, but is there a way to do it without calling out each column explicitly? I have a lot of columns and the table may evolve. I was hoping to keep it more genertic and flexible. — Timothy Clotworthy
– Timothy Clotworthy, Commented Jul 21, 2022 at 17:10
@BryanDellinger Or can you add something to show me how to constraint to a specific row at least? Thanks again! — Timothy Clotworthy
– Timothy Clotworthy, Commented Jul 21, 2022 at 17:23
@BryanDellinger ok this worked. I figured out your shorthand,,, thanks again — Timothy Clotworthy
– Timothy Clotworthy, Commented Jul 21, 2022 at 18:12

Adrian Klaver · Accepted Answer · 2022-07-21 18:16:16Z

1

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.

edited Jul 21, 2022 at 18:16

answered Jul 21, 2022 at 18:01

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Timothy Clotworthy Over a year ago

Adrian, I am not really following part of your answer. where you have value, do you mean column name??

Adrian Klaver Over a year ago

Yes, I will change the answer to reflect.

Timothy Clotworthy Over a year ago

thanks! works great. A little simpler than Bryans answer (I appreciate both!)

Adrian Klaver Over a year ago

Mostly it was about the fact that NULLIF exists and is a handy function.

Pepe N O · Accepted Answer · 2022-07-21 19:03:47Z

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);

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related