UPDATE RECORDS WITH AGGREGATE FUNCTION

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UPDATE RECORDS WITH AGGREGATE FUNCTION

Author

Topic

Fazeel
Starting Member

2 Posts

Posted - 2007-03-02 : 12:48:34

Problem is very simple

I want to update sum of a field from another table to first table

TABLE ONE:
==========
ItemID
QtyInStock

Table TWO:
==========
BatchID
ItemID
Qty

I want to Update the QtyInStock of First Table with Sum(Batch.Qty)

here is the query i am writing but giving error

UPDATE ITEMS
SET
INSTOCKQTY=CASE WHEN QtyInBatch>1 THEN QTYINBATCH ELSE 0 END
FROM ITEMS, (
SELECT ITEMS.ITEMID, SUM(Batch.Qty) AS QtyInBatch FROM Batch INNER JOIN Items ON Batch.ItemID = Items.ItemID GROUP BY ITEMS.ITEMID
)

appericiating anyones help in advance

FAZEEL AMJAD
Systems Engineer
Crystal Technologies

Fazeel
Starting Member

2 Posts

Posted - 2007-03-02 : 12:55:47

WELL HERE I GOT THE ANSWER

POSTING FOR OTHERS

UPDATE ITEMS
SET INSTOCKQTY=(SELECT SUM(Batch.Qty) AS QtyInBatch FROM Batch INNER JOIN Items ON Batch.ItemID = Items.ItemID GROUP BY ITEMS.ITEMID)
FROM ITEMS, BATCH
WHERE ITEMS.ITEMID=BATCH.ITEMID

FAZEEL AMJAD
Systems Engineer
Crystal Technologies

Stevo
Starting Member

8 Posts

Posted - 2007-03-02 : 14:17:50

Here, I'll go you one further:

UPDATE A
SET INSTOCKQTY= b.QtyInBatch
FROM ITEMS a with(nolock) inner join
( SELECT BatchItemID, SUM(Batch.Qty) QtyInBatch
FROM Batch GROUP BY BatchItemID)
on a.ItemID=b.BatchItemID

--Should always use ANSI syntax

+Stevo

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-03-04 : 15:13:03

I dunno... to me, saying you should always use ANSI syntax is like saying you have to do cube roots on a scientific calculator using only the 4 basic math functions because 4 function calculators don't have it... and the first time you run across some date math, you're toast... the only reason people say to stick to ANSI is because of some notion that the code will actually be portable... so I ask, 1) how often do you move between RDBMS engines and 2) why do you want to give up all that power offered in non-Ansi extensions of each?

Further, using correlated subqueries to do UPDATEs in SQL Server comes at a pretty high cost, performance wise. Each additional column updated will almost double the execution time...

Try this instead...

UPDATE a
    SET InStockQty = b.QtyInBatch
   FROM ITEMS a,
        (
         SELECT BatchItemID, SUM(Batch.Qty) AS QtyInBatch 
           FROM Batch 
          GROUP BY BatchItemID
        ) d
  WHERE a.ItemID = d.BatchItemID

--Jeff Moden

Stevo
Starting Member

8 Posts

Posted - 2007-03-05 : 11:06:00

When in Rome my friend.
Using ANSI with SQL Server's engine is a win-win.
I write many a syntax for Structured Query Language based on what platform/product I'm using, that's all.

+Stevo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources