Problem with Update statement and two tables... (1 Viewer)

[foody]

Hi,

I want to update the column qtyOpen from the table InventoryComponent in a condition that the ComponentNumber from InventoryTable and OrderNumber from the CompanyPurchasesDetailsComponents and PackageItem from the CompanyPurchasesDetailsComponents tables meet. I think I am getting there but unfortunatly it keeps updating 11 rows. It should only update one row. I don't know what I am doing wrong. Below is the SQL statement:

UPDATE InventoryComponent
SET QtyOpen = 31
WHERE EXISTS
((SELECT QtyOpen
FROM CompanyPurchasesDetailsComponents INNER JOIN InventoryComponent ON CompanyPurchasesDetailsComponents.PackageItem = InventoryComponent.PackageItem
WHERE (CompanyPurchasesDetailsComponents.OrderNumber = '12345-1') AND (CompanyPurchasesDetailsComponents.PackageItem = 'PE 55812') AND (InventoryComponent.ComponentNumber = 'PE 55812 NEWREL')))


Can anyone help me as soon as possible? Thanks in advance.

 

[pdx_man]

Try:

UPDATE InventoryComponent
SET QtyOpen = 31
FROM CompanyPurchasesDetailsComponents
INNER JOIN InventoryComponent
ON CompanyPurchasesDetailsComponents.PackageItem = InventoryComponent.PackageItem
WHERE (CompanyPurchasesDetailsComponents.OrderNumber = '12345-1') AND (CompanyPurchasesDetailsComponents.PackageItem = 'PE 55812') AND (InventoryComponent.ComponentNumber = 'PE 55812 NEWREL')))

You were only checking to see if the row exists, and if it does, update the whole table.