CODEWITHSUNDEEP
sqlsql-serverdatabasesql-update
lukejkw
lukejkw

Reputation: 1134

Update from inner join

I can't seem to get my update from inner join method to work. I have tried to write it a few different ways to no avail. Rows get affected but the product table's values are never actually updated.

Select code to test (should return no results after update query is run):

SELECT 
    [P].[ProductCode],
    [P].[PriceSelling],
    [ID].[InclPrice],
    [P].[StockQty],
    [ID].[Qty]
FROM
    dbo.Sync_ImportData AS [ID]
INNER JOIN
    dbo.Sync_Product AS [P]
ON
    [P].[ProductCode] = [ID].[ISBN]
WHERE
    ([P].[StockQty] <> [ID].[Qty] OR [P].[PriceSelling] <> [ID].[InclPrice])

Update Code:

UPDATE [P]
SET
    [P].[StockQty] = [ID].[Qty],
    [P].[PriceSelling] = [ID].[InclPrice]
FROM
    dbo.Sync_Product AS [P]
INNER JOIN
    dbo.Sync_ImportData AS [ID]
ON
    [P].[ProductCode] = [ID].[ISBN] 
WHERE
    ([P].[StockQty] <> [ID].[Qty] OR [P].[PriceSelling] <> [ID].[InclPrice])

Basically I need to update the stock qty and price if the Imported Data a) has a record for the product (ISBN matches ProductCode) and b) either value needs to be updated (qty or price is different from ImportData)

Upvotes: 2

Views: 98

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271211

You could see this behavior if there were more than one match between the tables. In particular, if ISBN is not unique in Sync_ImportData:

SELECT id.ISBN, COUNT(*)
FROM dbo.Sync_ImportData id
GROUP BY id.ProductCode
HAVING COUNT(*) > 1

If there are duplicates, then one of the rows would be used for the update -- arbitrarily. The changes would not match the second row.

Upvotes: 1

Related Questions