I have two tables parent and child with a one-to-many relation. I have a value in the child table that is always the same for a given parent ID. Therefore, I want to copy the value to a newly created column in the parent table.
| PARENT_ID | VALUE |
|---|---|
| 1 | null |
| 2 | null |
| CHILD_ID | PARENT_ID | VALUE |
|---|---|---|
| 1 | 1 | VALUE_1 |
| 2 | 1 | VALUE_1 |
| 3 | 1 | VALUE_1 |
| 4 | 2 | VALUE_2 |
| 5 | 2 | VALUE_2 |
I am using Liquibase and looking for a solution that works with Oracle and H2 (in Oracle mode).
This gives me a syntax error but the nested SELECT works:
UPDATE (SELECT DISTINCT PARENT.PARENT_ID, CHILD.VALUE as OLD_COLUMN, PARENT.VALUE as NEW_COLUMN
FROM PARENT
LEFT JOIN CHILD
ON PARENT.PARENT_ID = CHILD.PARENT_ID) t
SET t.NEW_COLUMN = t.OLD_COLUMN;
Output of nested SELECT:
| PARENT_ID | OLD_COLUMN | NEW_COLUMN |
|---|---|---|
| 1 | VALUE_1 | null |
| 2 | VALUE_2 | null |
It doesn't have to be a SQL solution, a Liquibase Update would be even better.
I think this sql will achieve what you are looking for:
update parent p
set value = (select distinct value
from child c
where c.parent_id = p.parent_id);