Compex "Update" from Cursor based to Set based

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

SQL Server 2000 Forums

Transact-SQL (2000)

Compex "Update" from Cursor based to Set based

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-11-09 : 07:07:09

Cesar writes "Please, take a look below (SET based option) ... the SUM for each UPDATE is wrong ...

Thanks.
Cesar

-- *** CURSOR based ***
Declare CSR_ORDDETAIL Cursor Fast_Forward For
Select ORDDETAIL.ID_PRODUCT, ORDDETAIL.QTY_PRODUCT
From ORDDETAIL
Inner Join PRODUCT On ORDDETAIL.ID_PRODUCT = PRODUCT.ID_PRODUCT
Where ORDDETAIL.FLAG_STATUS = 'OK' And PRODUCT.FLAG_STATUS = 1

Open CSR_ORDDETAIL

Fetch Next From CSR_ORDDETAIL Into @PRODUCT, @QTY

Begin Transaction
While @@Fetch_Status = 0 Begin

Update STOCK
Set STOCK.QTY_PRODUCT = (STOCK.QTY_PRODUCT + @QTY)
Where STOCK.ID_PRODUCT = @PRODUCT

If @@ERROR <> 0 Begin
Close CSR_ORDDETAIL
Deallocate CSR_ORDDETAIL
Rollback Transaction
Return
End

Fetch Next From CSR_ORDDETAIL Into @PRODUCT, @QTY
End
Commit Transaction

Close CSR_ORDDETAIL
Deallocate CSR_ORDDETAIL

-- *** SET based ***
Begin Transaction

Update STOCK
Set STOCK.QTY_PRODUCT = (Select SUM(ORDDETAIL.QTY_PRODUCT)
From ORDDETAIL
Inner Join PRODUCT On ORDDETAIL.ID_PRODUCT = PRODUCT.ID_PRODUCT
Where ORDDETAIL.FLAG_STATUS = 'OK' And PRODUCT.FLAG_STATUS = 1)
From ORDDETAIL
Inner Join PRODUCT On ORDDETAIL.ID_PRODUCT = PRODUCT.ID_PRODUCT
Inner Join STOCK On PRODUCT.ID_PRODUCT = STOCK.ID_PRODUCT

Commit Transaction"

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-11-10 : 08:24:13

Try this query

Begin Transaction

Update s
Set s.QTY_PRODUCT = (Select SUM(ORDDETAIL.QTY_PRODUCT)
From ORDDETAIL Inner Join PRODUCT On ORDDETAIL.ID_PRODUCT = PRODUCT.ID_PRODUCT and
PRODUCT.ID_PRODUCT = s.ID_PRODUCT
Where ORDDETAIL.FLAG_STATUS = 'OK' And PRODUCT.FLAG_STATUS = 1)
From STOCK s

Commit Transaction

It would be better if you could give table structures with some sample data.

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-11-10 : 08:25:52

sorry use it in the WHERE

and
PRODUCT.ID_PRODUCT = s.ID_PRODUCT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources