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)

The error message is "invalid identifier".

You need to check for the proper field names in the SQL statement.

Member Avatar

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');
commit;
Member Avatar

hfx642

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';