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

Best way to avoid duplicates in table?

user5920 Published in September 19, 2018, 11:40 am

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?

share|improve this question
  • Actually, the best and easiest way is to let the database raise that error an handle it. – Zohar Peled Apr 16 at 18:45
  • I think you need to properly write your UPDATE statement so that it doesn't produce duplicates. Perhaps... write an insert to a new table that changes everything (that isn't uniquely keyed) then SELECT DISTINCT * FROM <temp table>; into your final table. Or something like that. – JNevill Apr 16 at 18:50

2 Answers 2

active oldest votes
up vote 0 down vote
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO