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
x
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;