INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Can't update field in unique index because intermediate values conflict with existing rows

Can't update field in unique index because intermediate values conflict with existing rows

Can't update field in unique index because intermediate values conflict with existing rows

(OP)
This just infuriated me because I never ran across it before.

I have a table with a composite primary key.

One of those values, we'll call it A and the other we'll call be

So for data that looks like...

A B
1 B
2 B
3 B
4 B

A query...

CODE

Update SomeTable
Set SomeTable.A = SomeTable.A + 1
WHERE SomeTable.B = 'B' And A >= 2 

Fails with a duplicate records message for several of the records. In reality my 'A' records are from 1-8 and 6 records fail.

I had never run across this issue before and I expected it to work.... Just mad that the Engine is updating the records one at a time from a Declarative programming statement.

I'm going to have to start at the end of the records in a recordset and increment them that way instead to workaround...

In this case A is used for sorting the records so when records are inserted between two records, a gap has to be created. In this case I was debugging new code that used a test case of inserting one record and this query ran but had unexpected results. Running interactively revealed the issue.

In any case, the workaround does work.

RE: Can't update field in unique index because intermediate values conflict with existing rows

"I have a table with a composite primary key."
I assume the PK the composite of A and B

Your Update statement will fail because you are trying to make this happen:
A B
2 B      (Set SomeTable.A = SomeTable.A + 1)
2 B
3 B
4 B
 
You already have a record with A = 2 and B = 'B' and you cannot make another record with those values as PK.

Your composite primary key has to be unique to be a PK.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Can't update field in unique index because intermediate values conflict with existing rows

(OP)
And when it is done all the values will be part of a unique key.

RE: Can't update field in unique index because intermediate values conflict with existing rows

"when it is done all the values will be part of a unique key" - yes, but until then some values will be repeated (see my previous post), and that's a problem.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Can't update field in unique index because intermediate values conflict with existing rows

(OP)

Quote (OP)


Just mad that the Engine is updating the records one at a time from a Declarative programming statement.

You say the data is the problem, I say the Database Engine is the problem.

RE: Can't update field in unique index because intermediate values conflict with existing rows

Well, it is easy to point to the data base engine, but that will not get you anywhere.

To prove your point (or mine) you may try to do it the other way (backwards): start from the bottom and work your way up. In column A change 4 to 5, 3 to 4, 2 to 3, etc. and see if the Data base engine will complain about it.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Can't update field in unique index because intermediate values conflict with existing rows

(OP)

Quote (OP)


I'm going to have to start at the end of the records in a recordset and increment them that way instead to workaround...

...

In any case, the workaround does work.

My point is SQL (query) is a Declarative programming language and should simply do what it is told to do and figure all the procedural stuff out on its own. It doesn't in this case which is a database engine level failing. It surprised me. I expected it to work correctly. What I had hoped was there was some nuance to the query that could be changed to make it simply work instead of going the slower procedural route. That doesn't seem to be the case.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close