Flawed SQL Procedure

I am using the below procedure to set the field "Completed" to "True" in the table "Orders" only when the customer have paid and received or downloaded all his produts.

~~~~~~~~~~~~~~~~~~~~~~~~~

ALTER PROCEDURESetOrderToCompleted

(@.UserNameVARCHAR(50))

AS

UPDATEOrders

SETCompleted = 1

WHEREUserName = @.UserName

ANDCompleted = 0

~~~~~~~~~~~~~~~~~~~~~~~~~

Which is obviously flawed because I predict a situation where thesame customer ( user1 ) could havetwo different orders, like in the below example, when this procedurewill set incorrectly both fields "Completed" to "True" ( in tableOrders ) forOrderID = 1 and OrderID = 2 when actually thenot-downloadable product "gadget105" wasnot received yet by the customer (Received=False in table "OrderDetails" ).

Observations:

1)Downloadable products likesoftware have their field "Received" set toNULL because theydo not need to be shipped and therefore completing this field is irrelevant.

2) Both orders (OrderID = 1 and 2 ) were made by thesame customer withUserName = "user1".

3) The above procedure is only executed after all the downloadable products of the order have being downloaded by the customer.

Table OrderDetails

_______________________________________________________________

OrderID ProductID ProductName Downloadable Quantity Received UnitCost

1 10 software10 True 1 NULL 15.00

1 101 gadget101 False 1 True 20.00

2 12 software12 True 1 NULL 16.00

2 105 gadget105 False 1 False 22.00

2 13 software13 True 1 NULL 22.00

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Table Orders

_______________________________________________________________

OrderID UserName PaymentConfirmed Completed

1 user1 True False

2 user1 True False

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

How to solve the problem ?

You woul dprobably need to use the OrderId also in the WHERE clause.. so only the specific orders get "completed"|||

Hi ndinakar

But that is the problem, the procedure itself has to be capable to find out which OrderIDs must set the field Completed to True in the table .

|||

I can obtain the information that all downloadable items were downloaded by the client by verifing the field "RemoveRole" in theCustomerDownload table ( not shown here ) set to 'yes'.

Based on that, I devised this new procedure but since I am not good with "INNER JOINs", can somebody tell me if it is correct ?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ALTER PROCEDURESetOrderToCompleted

(@.UserNameVARCHAR(50))

AS

UPDATEOrders

SETCompleted = 1

WHEREOrderID = (SELECT OrderID

FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrdeID

INNER JOIN CustomerDownload ON Orders.OrderID = CustomerDownload.OrderID

WHERE Orders.UserName = @.UserName

ANDCustomerDownload.RemoveRole='yes'

AND OrderDetails.Received = 1

AND Orders.Completed = 0)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In this procedure I aim to set the field Completed to True for all the customer's orders that satisfy the above conditions.

|||

If your subquery returs multiple records your query could fail. Perhaps you might want to do an IN instead of "=".

UPDATEOrders

SETCompleted = 1

WHEREOrderID IN (SELECT OrderID

FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrdeID

INNER JOIN CustomerDownload ON Orders.OrderID = CustomerDownload.OrderID

WHERE Orders.UserName = @.UserName

ANDCustomerDownload.RemoveRole='yes'

AND OrderDetails.Received = 1

AND Orders.Completed = 0)