using inventorytableadapter.updateQuery1("itemno")
I'm able to subtract Invoicedetails "Qty" from Inventory "Instock" but I need to add a where clause to this query so that only the selected rows are updated here's my query
|
UPDATE Inventory where (Itemno=@ItemNo) Any Ideas
|
help with update statement
CoderD
I don't understand exactly what your problem is, so maybe a better explanation would be handy. What do you mean with selected records, because the Select query in you post selects all the itemNo that equal @itemNo. So in that case the Where clause itemNo = @itemNo would do.
If you select query is different you can add it to the where clause as a subselect.
greetz,
gert
Becker2
Yes I can add the Where clause but it doesn't solve the problem.
I need the update to only affect selected records. Even with the Where clause it is selecting previously selected records as well as currently selected records.
Chimme
Hi,
If you use the update statement with the WHERE clause, it only updates the records for the given ItemNo.
Is the relationship between Inventory and InvoiceDetails a 1 on 1 relationship If not, it is better to do a group by before doing the substraction.
Greetz,
Geert
Geert Verhoeven
Consultant @ Ausy Belgium
My Personal Blog
Helen Cool Granny
Hi,
I'm not sure if I get the problem right but you can add the WHERE clause to your UPDATE clause without any problems. Like this:
UPDATE Inventory
SET InStock = Inventory.InStock - InvoiceDetails.QTY
FROM Inventory INNER JOIN
InvoiceDetails ON Inventory.ItemNo = InvoiceDetails.ItemNumber
WHERE (Itemno=@ItemNo)
Greetz,
Geert
Geert Verhoeven
Consultant @ Ausy Belgium
My Personal Blog