Hi Ben,

> I have two tables, product and temp with the following
> structures
>
> product
> -------
> id integer NOT NULL (PK)
> desc varchar(50) NOT NULL
> stock integer NOT NULL
> rate decimal( 8, 2) NOT NULL
> rate tax( 5, 2) NOT NULL
>
> temp
> ----
> product_id integer NOT NULL (PK)
> stock integer NOT NULL
>
> temp table contains a few products with stock. When I try to
> run the following SQL statment it returns NULL ERROR in
> column stock.
>
> UPDATE product P
> SET P.stock = (select T.stock from temp T
> where P.id = T.product_id) ;
>
> Is the statment is trying to update stock in table product
> with null values for products which does not exist in table
> temp ? If it is, is there a work around ?

you probably should only update those products with existing
product related records in temp?

Try:

UPDATE product P
SET P.stock = (select T.stock from temp T
where P.id = T.product_id)
WHERE EXISTS (select 1 from temp T1 where P.id = T1.product_id);



Best Regards,
Thomas Steinmaurer

The IB LogManager Product Family
Logging/auditing suite for InterBase and Firebird
http://www.iblogmanager.com