Hi, i have 3 tables in oracle sql developer (packing, detail, header). I need to update the packing table's part no with header table's part no. But header doesn't contain the serial id hence i need to refer to detail table for cross reference. I did like below, but it throws an error. Please help to give an idea.

update packing
set packing.no = header.no where
(packing.id like 'R%' and
packing.no = 'N' and
packing.id = detail.id and
packing.box = header.box and
header.box = detail.box)
6 Years
Discussion Span
Last Post by hfx642

In THIS case, your Update statement only understands the table Packing.
In order to get Header.No, you'll have to use a sub-query and adjust EACH of your Where clauses accordingly.


Thanks a lot for the replies friends. I got it now. I need to change the statement. Sub-query is the way. Sorry, I'm still a novice to pl/sql.

update packing
set packing.no = (Select header.no 
                  from header 
                  where header.box = packing.box)
where (packing.id like 'R%' and packing.no = 'N');

They ya go. And, to make it easier (less typing), you can us "aliases".

update packing P
set P.no = (Select H.no 
            from header H
            where H.box = P.box)
where P.id like 'R%' and P.no = 'N';
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.