Update a table using data of other table

I am using Visual Studio 2008 and Sql Server 2005

I want to update a table using values from other table 开发者_如何学GoI have written a query but it is giving error

"Cannot insert the value NULL into column 'Quantity', table 'Stationarymgmt.dbo.Item_Master'; column does not allow nulls. UPDATE fails."

temp table has following columns Item_Code, Quantity, Cost , Name , Decription,

Item_Master table has follwing Columns Item_Code, Name, Decription, Cost , Quantity,

The query is

    UPDATE Item_Master,temp
 SET Item_Master.Quantity = Item_Master.Quantity - temp.Quantity where Item_Master.Item_Code = temp.Item_Code

Please help me out

---

You could rewrite it using SQL Server's update ... from:

UPDATE  im
SET     Quantity = im.Quantity - temp.Quantity
FROM    Item_Master im
JOIN    temp
ON      im.Item_Code = temp.Item_Code
WHERE   temp.Quantity is not null

The where condition should filter out rows from temp which lack a quantity.

---

Remember that Value - NULL = NULL the same way as 'String' + NULL = NULL

So either

UPDATE Item_Master,temp
SET Item_Master.Quantity = Item_Master.Quantity - ISNULL(temp.Quantity, 0) 
WHERE Item_Master.Item_Code = temp.Item_Code

OR

UPDATE Item_Master,temp
SET Item_Master.Quantity = Item_Master.Quantity - temp.Quantity 
WHERE Item_Master.Item_Code = temp.Item_Code
AND temp.Quanity IS NOT NULL