Skip to content
Advertisement

Fill Array from another column SQL

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;

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement