Update query help


I have the below SQL.
I need to update blLetterOfCredit.[GuaranteeCode]  to the value in Import-csm.[Guarantor Code]
I think I have it right but it gives me an error that basically says it cant update due to key violations.

Import-csm.[Guarantor Code] and tblLetterOfCredit.GuaranteeCode are both the same property in the tables as Double, General Number and Indexed No duplicates.  I initially had Import-csm.[Guarantor Code] as a Primary Key thinking that might be the issue but I removed it and still have the same error.  

What do you think could be the cause of this error?  

here is the SQL:
UPDATE [Import-CSM] INNER JOIN tblLetterOfCredit ON [Import-CSM].[Reference Number] = tblLetterOfCredit.LCNo SET tblLetterOfCredit.GuaranteeCode = [Guarantor Code];
 Qry Design Error
pdvsaProject financeAsked:
Who is Participating?
Bill RossCommented:

Since you have them indexed as no duplicateds then there is probably a duplicate.  Check for that.

pdvsaProject financeAuthor Commented:
and they are both number fields.  
Is  tblLetterOfCredit.GuaranteeCode involved in any indexes that may be defined as unique?
Bill RossCommented:
Specifically check for duplicate [Import-CSM].[Reference Number]  or tblLetterOfCredit.LCNo.  If either of these tables contain duplicates the the update will fail since there is more than one match in the inner join.
pdvsaProject financeAuthor Commented:
darn duplicates.   thx
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.