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;