Home Best way to avoid duplicates in table?
Reply: 0

Best way to avoid duplicates in table?

user1020 Published in July 17, 2018, 7:23 pm

I've been given a task that requires writing a script to mass change items in a table(ProductArea):

ProductID int
SalesareaID int

One ProductID can only exist once in each SalesareaID so there can't be any duplicates in this table. But one ProductID can be sold in multiple SalesareaID. So an example would look something like:

ProductID SalesareaID
1         1
1         2
1         3
2         2
3         1

Now, some areas have merged. So, if I try to run a straight-forward UPDATE to fix this like:

UPDATE ProductArea SET SalesareaID = 4 where SalesareaID IN (2, 3)

it will find (1, 2) and change that to (1, 4). Then it will find (1, 3) and try to change that to (1, 4). But that already exist so it will crash with a "Cannot insert duplicate key..."-error.

Is there a best/recommended way to tell my UPDATE to only update if the resulting (ProductID, SalesareaID) doesn't already exist?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.334486 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO