Update Statement

[shena]

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)

[debasisdas]

What is the error message ?

[shena]

The error message is "invalid identifier".

[debasisdas]

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

[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.

[shena]

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;

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