Update from inner join

Question

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)

I don't see any obvious reasons that wouldn't operate as expected. You could use the OUTPUT clause as per here tech-recipes.com/rx/47032/… to confirm which rows were updated. Are the columns updated float by any chance? — Nick.Mc
– Nick.Mc, Commented Jul 7, 2015 at 10:28
No null values should match on the JOIN and WHERE clause. The weird part is that the query runs, rows are said to be affected but when the data is inspected nothing has changed. @TimSchmelter — lukejkw
– lukejkw, Commented Jul 7, 2015 at 10:32
Yes, ProductCode and ISBN will match @Donal. The datatype for the columns being compared are the same: money for InclPrice and PriceSelling and INT for Qty and StockQty — lukejkw
– lukejkw, Commented Jul 7, 2015 at 10:38

Gordon Linoff · Accepted Answer · 2015-07-07 10:35:21Z

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.

Gordon you are absolutely right. I have just established this huge issue with what was supposed to be a reliable datasource. Thank you for your answer. Now it is time for a scathing email :)

Collectives™ on Stack Overflow

Update from inner join

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related