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;