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

[lameid]

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...

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.

 

[Andrzejek]

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:[pre]
A B
[highlight #FCE94F]2[/highlight] B (Set SomeTable.A = SomeTable.A + 1)
2 B
3 B
4 B
[/pre]
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.

 

[lameid]

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

 

[Andrzejek]

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.

 

[lameid]

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.

 

[Andrzejek]

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.

 

[lameid]

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.