Re: update ... easy one

I think i found something that works... but it should be possible to do it better. (problem was a high number of duplicates in both tables, some null fields etc.):
update c

set c.ID_PARENT = (select max(w1.ID_NEW_PARENT)

from w w1

where c.ID_PARENT = w1.id_CURR_PARENT

and c.ID_CHILD = w1.id_child

and w1.ID_NEW_PARENT != c.ID_PARENT -- can be left out i guess

and (c.ID_CHILD, w1.ID_NEW_PARENT) not in

(select c1.OID_CHILD, c1.ID_PARENT

from c c1)

)

where (c.id_parent,c.id_child) in

(select id_curr_parent, w.id_child

from w

where w.ID_NEW_PARENT != c.ID_PARENT

and ( c.ID_CHILD, w.ID_NEW_PARENT) not in

(select c2.ID_CHILD, c2.ID_PARENT

from c c2)

) Received on Wed Oct 30 2002 - 14:10:33 CST