0

I am running in to a primary key violation. My target DE has 2 primary keys (ID+System) where there can be duplicate IDs and my source DE has primary key as ID. while update a column in Target DE based on matching ID, it throws me a primary key violation error

I cannot change the format in both source and target DE and have to so something with the query.

SELECT
a.ID as ‘ID’,
a.System as ‘System’,
a.Updated as ‘Updated’,
a.LocalId as ‘LocalId’,
‘TRUE’ as ‘IsDeleted’

FROM
[Contact Source] a Inner JOIN DeletedInvalid_Staging b on a.ID=b.ID

Could you suggest an solution for this?

  • Why don't you output Source field in your query, even though it is primary key in your target DE? – Lukas Lunow Oct 24 '19 at 9:18
  • my mistake. I meant System. – Shruthy Nair Oct 24 '19 at 12:22
0

What type of method are you using to insert data into the destination DE? (Overwrite, Add, Update)

If Overwrite - you can try using a (Select DISTINCT * FROM [TABLE]) to make sure the result of your query is truly distinct and you don't have duplicate keyed records somewhere.

If Add - the data could already exist in the DE - try doing an Add/Update or just an Update instead or you can add an Where NOT EXISTS clause in your query...

SELECT
a.ID as ‘ID’,
a.System as ‘System’,
a.Updated as ‘Updated’,
a.LocalId as ‘LocalId’,
‘TRUE’ as ‘IsDeleted’
FROM
[Contact Source] a
Inner JOIN
DeletedInvalid_Staging b
on a.ID=b.ID
WHERE NOT EXISTS (Select 1 FROM [Your Destination DE] t1 WHERE a.ID = t1.ID AND a.System = t1.System)

If Update - keep in mind, the result of the query you are inserting isn't inserting record by record - overwriting a previous record it just updated. The results have to be distinct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.