Marketing cloud sql query execution error - Violation of PRIMARY KEY constraint

Question

I'm getting following error during update activity. Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'Enq_opp_Parallel_ContactView'. The duplicate key value is (0061M000010wwpLQAQ)

Getting this error even though source table contains unique values. Also this error only occurs during update activity. If I do override then it's works fine.

Here is the sql query i'm using -

select
DISTINCT(OS.Id) as RecordId, CS._ContactKey as ContactKey,  'Opportunity' as RecordType,
'Contact' as ParentRecordType, AcS.AESL_Email_status__c  as EmailStatus, 
OS.AESL_Lead_Score__c as LeadScore,
OS.StageName as StatusStage, OS.AESL_Subdisposition__c as SubDisposition,
OS.AESL_Transaction_Status__c as TransactionStatus, OS.LastModifiedDate as LastModifiedDate


from Opportunity_Salesforce as OS
inner join Account_Salesforce as Acs on OS.AccountId = AcS.Id
inner join Contact_Salesforce as CS on AcS.Id = CS.AccountId
/* finding previous days date using current server datetime */
where convert(date,OS.LastModifiedDate) = convert(date,dateadd(minute,-30,dateadd(hour,-12,Getdate())))

Please not that RecordId is primary key here.

Answer 1

Found the root cause. This is happening because there are multiple rows exists in "Contact" object against single "Student" which usually is not the case in sales cloud. Because of this even if "Opportunity" object contains unique ids, post inner join I was getting multiple rows with same Opportunity Id (recordId).

I've found solution on the other thread on stackexchange only. Here is the link - Violation of PRIMARY KEY constraint. Cannot insert duplicate key