Multiple case in update postgres-CodePudding

I need to update 2 columns in table with same conditions. I know, that each of them would take a lot of time. How can I concatenate 2 updates into 1, which can be faster?

-- first update

update t1
set col1 =
case when cc1  is not NULL and cc1  <> 0 then 'A'
        when cc2 is not NULL and cc2 <> 0 then 'B'
        when cc3 is not NULL and cc3 <> 0 then 'C'
       else null
end;

-- with same cond

update t1 
             set    col2 =
             case when cc1  is not NULL and cc1  <> 0 then 'qwe rty'
                     when cc2 is not NULL and cc2 <> 0 then 'qzaz wsx'
                     when cc3 is not NULL and cc3 <> 0 then 'zxcv asdf'
              else 'pl ok'
             end;

-- my effort to concatenate, dont work

update  t1
             set    (col1, col2)  =
             (select c1, c2 from
              (select case when t2.cc1  is not NULL and  t2.cc1  <> 0 then 'A' as c1,  'qwe rty' as  c2
                     when t2.cc2 is not NULL and t2.cc2 <> 0 then ('B', 'qaz wsx')
                     when t2.cc3 is not NULL and t2.cc3 <> 0 then ('C', ' zxcv asdf')
              else (null, 'pl ok')
             end 
             from t1 as t2 where t1.key_column1 = t2.key_column1 and t1.key_column2 = t2.key_column2 and t1.key_column3 = t2.key_column3) f)

       ;

CodePudding user response：

This is the way I would do it.

WITH cte AS (SELECT * FROM 
             (VALUES(1, 'A', 'qwe rty'),(2, 'B', 'qaz wsx'),(3, 'C', 'zxcv asdf'),(4, NULL, 'pl ok')) v (id,c1,c2)) 
UPDATE so_demo
SET col1 = cte.c1, col2 = cte.c2
FROM cte WHERE cte.id = CASE WHEN COALESCE(cc1, 0) <> 0 THEN 1 
                             WHEN COALESCE(cc2, 0) <> 0 THEN 2
                             WHEN COALESCE(cc3, 0) <> 0 THEN 3 
                             ELSE 4 END;

By way of explanation, I have put the possible values into a cte assigning them an id in addition to the values. I can then put the case statement in the where clause generating the necessary id. Note the use of COALESCE to make the WHENs simpler to read.

CodePudding user response：

One way is to use arrays.

UPDATE t1 
       SET (col1,
            col2) = (SELECT x[1],
                            x[2]
                            FROM (SELECT CASE
                                           WHEN cc1 IS NOT NULL
                                                AND cc1 <> 0 THEN
                                             ARRAY['A',
                                                   'qwe rty']
                                           WHEN cc2 IS NOT NULL
                                                AND cc2 <> 0 THEN
                                             ARRAY['B',
                                                   'qzaz wsx']
                                           ...
                                           ELSE
                                             ARRAY[NULL,
                                                   'pl ok']
                                         END) AS x
                                                 (x));

But in terms of runtime optimization the gain compared to just UPDATE ... SET col1 = CASE ..., col2 = CASE ... should be neglectable, if any.