Error in adding the columns of two tables: SQL Server 2005

I'm stuck in this problem for quite while now. I have two tables ItemMaster and ItemStock and in ItemStock table I have a column ItemId, which is foreign key to Id column of ItemMaster table, and whenever I add new quantity in ItemStock table, I want that quantity value automatically gets summed with already exited quantity in the ItemMaster based on ItemId of ItemStock table.

ItemMaster:

   Id         ItemName     Quantity        
---------- -----------  ----------- 
    1           Item1       50
    2           Item2       50

ItemStock:

    Id         ItemId     Quantity        
---------- -----------  ----------- 
    1           1         20
    2           2         30

Query in SQL Server 2005:

with Developer([sum], itemid, stockid)
as 
(
    select 
       sum(stock.quantity + isNull(im.quantity, 0)) as [sum], 
       im.id as Itemid, stock.itemid as stockid 
    from ItemMaster im 
    inner join ItemStock stock on stock.itemid = im.id 
    group by im.id, stock.itemid 
)
update ItemMaster
set quantity = (Select [sum] from Developer) 

Results in an error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

Could anyone please tell me how ca开发者_如何学Cn I solve this problem?

Aren't you missing a WHERE clause in your UPDATE statement??

Right now, if you do a SELECT * FROM Developer, you get:

sum itemid  stockid
 70   1        1
 80   2        2

and that's exactly what the error says - the query returns more than a single results, so what is the UPDATE supposed to do with this?? It cannot set the quantity column to more than a single value.....

Just guessing - do you maybe mean to do this?

;WITH Developer.......
(
     ......
)
UPDATE dbo.ItemMaster
SET quantity = dev.sum
FROM Developer dev
WHERE dbo.ItemMaster.ItemId = dev.ItemId   

Add the WHERE clause to associate one row from Developer with a single row in ItemMaster

Your select return more than one records because you use a simple join, so you should insert a where clause to filter by id. Something like that

select sum(stock.quantity + isNull(im.quantity,0)) as [sum], im.id as Itemid, stock.itemid as stockid from ItemMaster im inner join ItemStock stock on stock.itemid = im.id group by im.id, stock.itemid
where im.id = @MyId and stock.itemid = @MyID2

I would also remove the other fields in the select, because you do not need them. Leave only the sum (a select more clear imo)

You could create a view:

CREATE VIEW ItemsOnHand
WITH SCHEMABINDING
AS
    SELECT ItemID,SUM(Quantity) as Quantity,COUNT_BIG(*) as Cnt
    FROM dbo.ItemStock

This view would always be correct. If performance is a concern, you could also index this view (on ItemID), which would mean that SQL Server would maintain it as a hidden table, automatically adjusting the quantity value as rows are inserted, deleted or updated in the ItemStock table.