Multiple case in update postgres

Question

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)

       ;

Why not just set col1=…, col2=… like a regular update? — Sami Kuhmonen
– Sami Kuhmonen, Commented Jan 10, 2022 at 11:11
Dont catch ... I want to compare case and use all three conditions once, not twice — yuoggy
– yuoggy, Commented Jan 10, 2022 at 11:32
Have you tried it and determined that the database engine doesn’t optimize it that way and it’s a problem? — Sami Kuhmonen
– Sami Kuhmonen, Commented Jan 10, 2022 at 11:33
I know the size of table t1 also I know that each of queries takes much time, I mean, I need one query, which will contain both previous — yuoggy
– yuoggy, Commented Jan 10, 2022 at 11:47
And I suggested such. If you haven’t tried it then how can you know it is slow and a problem? — Sami Kuhmonen
– Sami Kuhmonen, Commented Jan 10, 2022 at 12:34

Jonathan Willcock · Accepted Answer · 2022-01-10 11:50:19Z

2

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.

answered Jan 10, 2022 at 11:50

Jonathan Willcock

5,4303 gold badges25 silver badges36 bronze badges

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

1 Comment

sticky bit Over a year ago

Side note: CASE expression, not statement. SQL knows no flow control statements at all.

sticky bit · Accepted Answer · 2022-01-10 12:00:33Z

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.

Collectives™ on Stack Overflow

Multiple case in update postgres

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related