Needing to update multiple records...

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

SQL Server Development (2000)

Needing to update multiple records...

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-27 : 09:05:23

JM writes "Hi,

This seems really simple but for some reason I'm having a brainfart and can't figure it out.

What I'm needing to do is update multiple records in a table for a given cart id. Here's the layout I've got - I hope it comes through.

tbCart
------
CartID --------
FKClientID    |
              |     tbCartDetails
              ----  ------------- 
                 |--FKCartID
                    QuantityOrdered
                    FKProductID --------
                                       |
                                       |   tbProducts
                                       |   ----------
                                       ----ProductID
                                           StockedQuantity

So basically what's happening is that I pass in a CartID to a stored procedure which then needs to take that CartID and loop through tbCartDetails updating StockedQuantity in tbProducts with the QuantityOrdered.

An example would be:
CartID = 10
With 4 records of products for that CartID
QuantityOrdered = 5 w/a FKProductID of 4,
QuantityOrdered = 6 w/a FKProductID of 8,
QuantityOrdered = 1 w/a FKProductID of 2,
QuantityOrdered = 1 w/a FKProductID of 1
So I'd need to update tbProducts.StockedQuantity w/those 4 records for the appropriate FKProductID.

Hope that made sense and thanks!!"

MakeYourDaddyProud

184 Posts

Posted - 2002-06-27 : 09:12:07

update tbProducts
set StockedQuantity = CD.QuantityOrdered
from tbProducts P
join tbCartDetails CD
on CD.FKProductID = P.ProductID
join tbCart C
on C.CartID = CD.FKCartID
and C.CartID = @CartID -- Passed into proc

Hope that helps

Daniel Small MIAP
www.danielsmall.com IT Factoring

JM
Starting Member

2 Posts

Posted - 2002-06-27 : 10:16:29

That's exactly what I needed, thanks bud!

MakeYourDaddyProud

184 Posts

Posted - 2002-06-27 : 11:00:33

It might also be a good idea to wrap the thing in a rollback set

1) BEGIN TRAN

2) UPDATE statment

3) select @@rowcount
-- prove expected result count

If correct results then COMMIT the tran else ROLLBACK TRAN

Daniel Small MIAP
www.danielsmall.com IT Factoring

JM
Starting Member

2 Posts

Posted - 2002-06-28 : 06:20:37

Thanks,

I've actually got that one in there along with a few other checks and what-not.

Appreciate the heads-up!
- JM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources