CODEWITHSUNDEEP
sqlsql-server-2008asp-classic
RedsDevils
RedsDevils

Reputation: 1433

Classic ASP & sqlstatement : How to update 2 and more Master & detail records with Classic ASP?

Now I want to update table. Assumes that, I have PurchaseOrder and PurchaseOrderDetails.

I have one field called Status in PurchaseOrder table and Matched_Status Field in PurchaseOrderDetails. I need to update Status field to "1" if all PurchasedItems Quantity and Amount are same when we issue invoice. If PurchaseItems Quantity or Amount isn't matched, I need to update Status to "5".

For Matched_Status in PurchaseOrderDetails, Deafult is "0". And if Items Quantity and Amount are same, I need to update "1" to Matched_Status in PurchaseOrderDetails. I can do PurchaseOrderDetails updating.What I face order is in updating to PurchaseOrder.

I got an array of Purchaseorder Invoice No.

I make iteration with array count and update Status field in PurchaseOrder table with the following statement.

    UPDATE PurchaseOrder SET Status = CASE WHEN ((SELECT COUNT(*) FROM PurchaseOrderDetail WHERE CoID = 'SampleCoID' AND Matched_Status = 0 AND PurchaseOrderNo='PONo') <> (SELECT MAX(ItemsNo) FROM PurchaseOrderDetails WHERE AND Matched_Status = 0 AND PurchaseOrderNo='PONo')) THEN 5 AND 1 END WHERE CoID = 'SampleCoID' AND PurchaseOrderNo='PONo'

Note: ItemsNo field are save the PurchaseItems Number save for PurchaseOrderDetails. If we buy 3 items, Max(ItemsNo) will be 3. I write statement and loop with classic asp. Can you all show me what wrong at above statement and the example code of classic asp for that.

Thanks in advance! RedsDevils

Upvotes: 0

Views: 531

Answers (1)

pavanred
pavanred

Reputation: 13823

Yo can try this updates status in PUrchaseOrder table to "1" when Quantity and Amount fields in PurchaseOrder table are same as in Quantity and Amount fields Invoice table.

UPDATE PurchaseOrder SET Status = 1 
FROM PurchaseOrder PO 
INNER JOIN Invoice I On PO.Quantity = I.Quantity AND PO.Amount = I.Amount

I don't know the exact table and column names. If you provide the table structure and perhaps sample data and output then I can provide a better answer.

Upvotes: 1

Related Questions