SQL Update Key Violation

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • shredder249
    New Member
    • Jan 2008
    • 22
    #1

    SQL Update Key Violation

    Hi, I am trying to run an SQL update query but out of the 9 records it should be updating, it updates one and then says: "Database can't update all the records in the update query. 8 fields weren't updated due to key violations". The SQL statement is below:

    Code:
    DoCmd.RunSQL ("UPDATE [Track] SET [digital] = False, [URL] = '', [Valid Path] = False WHERE [Artist] LIKE [Artiststore] AND [Album] LIKE [Albumstore]")
    The access help didn't have anything on key violations...is it a problem with primary keys or something?
    Tags: None
    • puppydogbuddy
      Recognized Expert Top Contributor
      • May 2007
      • 1923
      #2
      Originally posted by shredder249
      Hi, I am trying to run an SQL update query but out of the 9 records it should be updating, it updates one and then says: "Database can't update all the records in the update query. 8 fields weren't updated due to key violations". The SQL statement is below:

      Code:
      DoCmd.RunSQL ("UPDATE [Track] SET [digital] = False, [URL] = '', [Valid Path] = False WHERE [Artist] LIKE [Artiststore] AND [Album] LIKE [Albumstore]")
      The access help didn't have anything on key violations...is it a problem with primary keys or something?
      In my experience, this problem can occur when the foreign key index on the many side of a one to many relation between 2 tables is set to no duplicates allowed, when it should be set to duplicates allowed. So check the index property setting in the foreign key table.

        Comment

        • NeoPa
          Recognized Expert Moderator MVP
          • Oct 2006
          • 32668
          #3
          It would appear that one of [digital], [URL] or [Valid Path] is defined as not allowing duplicates. Check out your table definition.

            Comment

            • shredder249
              New Member
              • Jan 2008
              • 22
              #4
              Cheers NeoPa and puppydogbuddy, the URL field was set to Yes (No Duplicates). But I don't understand why that caused the problem because the query should overwrite the URL field regardless of what data it contains shouldnt it?

              Originally posted by NeoPa
              It would appear that one of [digital], [URL] or [Valid Path] is defined as not allowing duplicates. Check out your table definition.

                Comment

                • NeoPa
                  Recognized Expert Moderator MVP
                  • Oct 2006
                  • 32668
                  #5
                  The query acted on the first record and set the [URL] field to an empty string (''). When it came to the second record it tried to do the same thing. As there was already a record in the table with a [URL] field with that same value ('') it could not be updated as that would involve adding a duplicate entry for that index. All subsequent records suffered from the same problem - hence only one was correctly updated.Does that make better sense?

                    Comment

                    Previous template Next
                    Working...