Home Best way to avoid duplicates in table?

Best way to avoid duplicates in table?

user5920
1#
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?

• 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