Question 115035 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Update first table based on multiple columns from another table

I have table1 as following;

a b c d e status
2 7 21 36 43

And table2 as following;

a b c d e
16 21 22 23 40
5 10 16 27 41
8 10 28 38 39
11 12 13 21 23
7 9 17 19 21

and would like to update table1.status by finding how many numbers from table1 is matched/available in table2

For instance, based on the above scenarios, the result will be 2 (as mentioned below) because there are only two numbers 7 & 21 in table2;

a b c d e status
2 7 21 36 43 2

Am using the below mentioned query and is working as expected but wanted any alternative shorter query

UPDATE table1 as t1 SET status = (
  CASE WHEN (SELECT SUM(CASE WHEN t1.a IN(t2.a, t2.b, t2.c, t2.d, t2.e) THEN 1 ELSE 0 END) FROM table2 AS t2)>0 THEN 1 ELSE 0 END+
  CASE WHEN (SELECT SUM(CASE WHEN t1.b IN(t2.a, t2.b, t2.c, t2.d, t2.e) THEN 1 ELSE 0 END) FROM table2 AS t2)>0 THEN 1 ELSE 0 END+
  CASE WHEN (SELECT SUM(CASE WHEN t1.c IN(t2.a, t2.b, t2.c, t2.d, t2.e) THEN 1 ELSE 0 END) FROM table2 AS t2)>0 THEN 1 ELSE 0 END+
  CASE WHEN (SELECT SUM(CASE WHEN t1.d IN(t2.a, t2.b, t2.c, t2.d, t2.e) THEN 1 ELSE 0 END) FROM table2 AS t2)>0 THEN 1 ELSE 0 END+
  CASE WHEN (SELECT SUM(CASE WHEN t1.e IN(t2.a, t2.b, t2.c, t2.d, t2.e) THEN 1 ELSE 0 END) FROM table2 AS t2)>0 THEN 1 ELSE 0 END )
Tags:

2

Answers


  1. 0

    As commented above, this data model and query request makes no sense.

    Use jsonb to pivot columns to rows:

    with targets as (
  select distinct v 
    from table2 t
         cross join lateral jsonb_each(to_jsonb(t)) as e(k, v)
), sourcequery as (
  select distinct a, b, c, d, e, v
    from table1 t
         cross join lateral jsonb_each(to_jsonb(t) - 'status') as e(k, v)
), matches as (
  select s.a, s.b, s.c, s.d, s.e, count(t.v) as status
    from sourcequery s
         left join targets t on t.v = s.v
   group by s.a, s.b, s.c, s.d, s.e
)
update table1
   set status = m.status
  from matches m 
 where m.a = table1.a
   and m.b = table1.b
   and m.c = table1.c
   and m.d = table1.d
   and m.e = table1.e;

    db<>fiddle here

    Login or Signup to reply.
  2. 0

    Agree with previous posters: this this is a terrible data model. But you can get what you are asking for by expanding the columns from table2 into individual rows then determining the distinct values contain as column value in table 1. Finally, count the results. (see demo)

    select sq.a,sq.b,sq.c,sq.d,sq.e,count(*) status 
from (
       select distinct on (v) t1.*,v
         from t1
        cross join 
              ( select a v from t2 union all
                select b   from t2 union all
                select c   from t2 union all
                select d   from t2 union all
                select e   from t2  
              ) cju 
          where cju.v in (t1.a,t1.b,t1.c,t1.d,t1.e)
      ) sq 
group by sq.a,sq.b,sq.c,sq.d,sq.e;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search