Update query help

Posted on 2011-10-28
Last Modified: 2013-11-27

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
Question by:pdvsa

    Author Comment

    and they are both number fields.  
    LVL 12

    Expert Comment

    Is  tblLetterOfCredit.GuaranteeCode involved in any indexes that may be defined as unique?
    LVL 14

    Accepted Solution


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

    LVL 14

    Expert Comment

    by:Bill Ross
    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.

    Author Closing Comment

    darn duplicates.   thx

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now