Ben,

>When I tried to update product name in product table using
>the UPDATE statment given below, it is trying to update all
>the records in the product table that doesnt have an id
>in new_product. So it resulted in null replacement in the
>product name and the UPDATE statment gets aborted since null
>value is not allowed in product name.
>
>UPDATE product P SET product_nm = (select product_nm
> FROM new_product NP where NP.id = P.id) ;

of course this fails. You ask it to update all records since you do not
have any limiting where clause. Most likely what you want is something like

UPDATE product P
SET product_nm = (select product_nm
FROM new_product NP where NP.id = P.id)
WHERE exists(select 1 FROM new_product NP2 where NP2.id = P.id)

TSH (this should help),
Set