0

I have tried to change the customerID number for the sales table but constantly facing constraints issues.

UPDATE  sales 
set s.customerId = '15'
from sales s
where s.customerId = '28'
and s.merchantType in ('TS', 'CT');

SQL Error [2601][23000] attempt to insert duplicate key in object 'sales' with unique index 'sales_PKC'
sales_PKC Primary Key Index (CustomerID, Order_ID): clustered, FOREIGN_REFERENCE

Basically i am trying to change records with CustomerID 28 and merchantType TS and CT to 15 but kept return with error shown below. It seems to be creating duplicate records but i am not sure how what to do without dropping the constraints. Any suggestion would be greatly appreciated.

EMPZ
  • 1
  • You might have to rethink your database constraint: does it correctly encode your business logic? And does this operation respect your business logic? It's not clear from what you're providing here how changing the customerId on those records makes sense, so I'm not sure we could answer your question as it stands. – joanis Oct 29 '22 at 16:14
  • Hi Joanis! Thank you for pointing it out. This is an old legacy system and the customer ID 16 and 28 are the same customer that accidentally created two ID. We are now trying to merge all the records together. Hopefully, this will clarify it a bit clearer. – EMPZ Oct 31 '22 at 15:31
  • OK, that does make sense. In that case, if you have a uniqueness constraint on (CostomerID, Order_ID) (if I'm reading your error log correctly), you might have to renumber the orders you are migrating. From the business logic, I would think that otherwise you'd be jumbling together two different orders for the same customer under the same order ID, which sounds like a mess to me that you probably want to avoid. – joanis Oct 31 '22 at 16:47

0 Answers0