Fill Array from another column SQL

Question

I need help whit my Code (PostgreSQL).

I get in my row Result {{2.0,10}{2.0,10}} but I want {{2.0,10}{3.0,20}}. I don't know where my error is. Result is text [ ] its must be a 2D Array

This is Table1

Nummer	Name	Result
01	Kevin

This is Table2

Nummer	Exam	ProfNr
01	2.0	10
01	3.0	20

My Code is

update public."Table1" as t1
   set "Result" = ARRAY[[t2."Exam", t2."ProfNr"],[t2."Exam",t2."ProfNr"]]
from public."Table2" as t2 
where t1."Nummer"= 01
and t2."Nummer"=01;

Mike Organek · Accepted Answer · 2021-02-08 23:28:42Z

1

You need to aggregate rows to make this happen. This CTE should help clarify what I mean:

with buildarr as (
  select nummer, array_agg(array[exam, profnr]) result
    from table2
   group by nummer
)
update table1
   set result = buildarr.result
  from buildarr
 where buildarr.nummer = table1.nummer;

Working fiddle

answered Feb 8, 2021 at 23:28

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2021-02-08 23:28:48Z

1

You need to pre-aggregate:

update public."Table1" as t1
   set "Result" = t2.ar
from (select array_agg(array[t2."Exam", t2."ProfNr"])) as ar
      from public."Table2" as t2 
      where t2."Nummer" = 01
     ) t2
where t1."Nummer"= 01;

answered Feb 8, 2021 at 23:28

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Fill Array from another column SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related