CODEWITHSUNDEEP
sqloracle-databaseparent-childhierarchy
user3075906
user3075906

Reputation: 745

Updating child rows based on parent values with Oracle sql

I have 2 tables in Oracle with structures similar to these:

family table

id member parent_id  flag flight
---------------------------------
1   A     null       N    null
2   B      1         N    null
3   C     null       N    null
4   D      3         N    null
5   E      3         N    null
6   F     null       N    null
7   G      6         N    null

flight table

id   family_id   flight
-----------------------

1      1         1000
2      3         2000

Here rows with id 4,5 in family table are child of Row 3 and Row 2 is child of 1.

Now, I need to write a oracle update sql to not only update the corresponding parent row with flight and change the flag to Y but child rows also should be updated accordingly. If there is no corresponding flight assignment for family in flight table then rows 6,7 should remain as such.

Doing parent update and finding child for a parent easy. But is it possible to do all updates in one sql?

Thanks.

Update

update family fm set (flag, flight) = 
(
  select 'Y', fl.flight  from flight fl where fm.flag <> 'Y' and
(
  (fl.parent_id=fm.id and fm.parent_id is null )
 or fm.parent_id=fl.parent_id
)
)
where exists (select 1 from flight fl where fm.id=fl.parent_id or fl.parent_id=fm.parent_id );

This works !!

Upvotes: 3

Views: 3858

Answers (2)

Florin
Florin

Reputation: 555

Another way to update in Oracle all the families,not just a particular one, could be this:

update family F
set flag='Y', 
FLIGHT=(
SELECT FLIGHT FROM FLIGHT WHERE FAMILY_ID=F.PARENT_ID OR f.id=family_id
)
WHERE ID IN (select id 
from family a 
start with id in (
select family_id from flight where a.id=family_id)
                     connect by prior id=parent_id);

Hope it helps.

Upvotes: 0

UtillYou
UtillYou

Reputation: 251

Yes you can .In oracle , it is easy to select recursively.Here is my suggestion :

Update family set flag='Y' 
    where id in(select id from family start with id=3 
                     connect by prior id=parent_id)

Upvotes: 1

Related Questions