• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

Update query help

Experts,

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
0
pdvsa
Asked:
pdvsa
  • 2
  • 2
1 Solution
 
pdvsaProject financeAuthor Commented:
and they are both number fields.  
0
 
pdebaetsCommented:
Is  tblLetterOfCredit.GuaranteeCode involved in any indexes that may be defined as unique?
0
 
Bill RossCommented:
Hi,

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

Bill
0
 
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.
0
 
pdvsaProject financeAuthor Commented:
darn duplicates.   thx
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now