Subtracting quantities when ordered?

[Newsgroup_User]

I need to be able to subtract the total quantity of a product when
someone places an order. I'm trying to get my head around this; what's
the strategy for doing something like this?

ASP SQL

[Newsgroup_User]

When order complete (paid for)
Retrieve quantity from in stock field in the products database
Subtract 1 and then UPDATE the in stock field

UPDATE tblProducts
SET QuantityInStock = %NewQuantity
WHERE ProductID = %ProductID




"Art" <lee_nospamification@artjunky.com> wrote in message
news:fgsn1f$pnp$1@forums.macromedia.com...
>I need to be able to subtract the total quantity of a product when someone
>places an order. I'm trying to get my head around this; what's the strategy
>for doing something like this?
>
> ASP SQL

[Newsgroup_User]

You knew I was going to have a follow-up, right?

What would the actual Stored procedure or asp look like that does the
actual subtraction?

Know of a tutorial somewhere?

Thanks for the quick response.

-m-@v@-m- wrote:
> When order complete (paid for)
> Retrieve quantity from in stock field in the products database
> Subtract 1 and then UPDATE the in stock field
>
> UPDATE tblProducts
> SET QuantityInStock = %NewQuantity
> WHERE ProductID = %ProductID
>
>
>
>
> "Art" <lee_nospamification@artjunky.com> wrote in message
> news:fgsn1f$pnp$1@forums.macromedia.com...
>> I need to be able to subtract the total quantity of a product when someone
>> places an order. I'm trying to get my head around this; what's the strategy
>> for doing something like this?
>>
>> ASP SQL
>
>

[Newsgroup_User]

I'm trying to visualize this.

So let's say I have a "Thank You" page at the end of the purchase.
I have a table with a list of product Ids and a column of quantities left.

If I understand your post, I'm picturing this being a stored procedure
that looks at the total quantities from my table that contains all the
product quantities. Let's say product 1a has a quantity of 5 and product
2a has a quantity of 6.

A bit fuzzy here but what would a stored procedure look like that is
able to update and step through all the items that were purchased?

[Newsgroup_User]

"Art" <lee_nospamification@artjunky.com> wrote in message
news:fgsq41$kt$1@forums.macromedia.com...
> I'm trying to visualize this.
>
> So let's say I have a "Thank You" page at the end of the purchase.
> I have a table with a list of product Ids and a column of quantities left.
>
> If I understand your post, I'm picturing this being a stored procedure
> that looks at the total quantities from my table that contains all the
> product quantities. Let's say product 1a has a quantity of 5 and product
> 2a has a quantity of 6.
>
> A bit fuzzy here but what would a stored procedure look like that is able
> to update and step through all the items that were purchased?


I think you need a join between products orders and orderdetails in an
update statement:

UPDATE tblProduct SET tblProduct.Quantity = tblProduct.Quantity -
tblOrderDetails.Quantity
FROM tblOrder INNER JOIN
tblOrderDetails ON tblOrder.OrderID =
tblOrderDetails.OrderID INNER JOIN
tblProduct ON tblOrderDetails.ProductID =
tblProduct.ProductID
WHERE (tblOrder.OrderID = 1)

[Newsgroup_User]

Joris van Lier wrote:
> "Art" <lee_nospamification@artjunky.com> wrote in message
> news:fgsq41$kt$1@forums.macromedia.com...
>> I'm trying to visualize this.
>>
>> So let's say I have a "Thank You" page at the end of the purchase.
>> I have a table with a list of product Ids and a column of quantities
>> left.
>>
>> If I understand your post, I'm picturing this being a stored procedure
>> that looks at the total quantities from my table that contains all the
>> product quantities. Let's say product 1a has a quantity of 5 and
>> product 2a has a quantity of 6.
>>
>> A bit fuzzy here but what would a stored procedure look like that is
>> able to update and step through all the items that were purchased?
>
>
> I think you need a join between products orders and orderdetails in an
> update statement:
>
> UPDATE tblProduct SET tblProduct.Quantity = tblProduct.Quantity -
> tblOrderDetails.Quantity
> FROM tblOrder INNER JOIN
> tblOrderDetails ON tblOrder.OrderID =
> tblOrderDetails.OrderID INNER JOIN
> tblProduct ON tblOrderDetails.ProductID =
> tblProduct.ProductID
> WHERE (tblOrder.OrderID = 1)
>
>
Thanks, I'll look that over.

[hatethisnamegame]

UPDATE tblProducts
SET QuantityInStock = QuantityInStock - %QuantityOrdered
WHERE ProductID = %ProductID

You may have to do a loop through all of the items in the order and run the above for each item

[Newsgroup_User]

"hatethisnamegame" <webforumsuser@macromedia.com> wrote in message
news:fh0q9k$dvj$1@forums.macromedia.com...
> UPDATE tblProducts
> SET QuantityInStock = QuantityInStock - %QuantityOrdered
> WHERE ProductID = %ProductID
>
> You may have to do a loop through all of the items in the order and run
> the
> above for each item

Yes that's another way to do it, however if you instruct the database to
update multiple records at once it will be able to optimize that operation.

Look at the delete with join statement, you can prevent a lot of data from
leaving and entering the process boundary of the database (for each update
you have to retrieve productid, Quantity in stock and pass them in again,
and the SQL has to be parsed every time).

In general try to perform as many operations as possible using a single
statement and try to keep them woithin the database engine, the point of a
database is to store, process and retrieve your data as efficiently as
possible.

Joris

[Newsgroup_User]

Thanks for the help on this...I'm so loaded down with other "issues"
that I can't look into it too much right now.

I posted here to get the "ball rolling." I'll probably be back with many
more questions when I run into the proverbial brick wall on this.

Thanks again.