strange error on query execution

[Alan Lambert]

I'm using SQLCE 3.5

I have a table that is storing an order list with 2 fields ID and
Description. ID is sequentailly numbered.

I want to insert 2 extra items in the middle of the list (before item 5,
let's say) and run the following query:

UPDATE myTable
SET Id=Id+2
WHERE Id>=5

To move all items greater than 5 'up' 2 places so I can then insert the new
records. However, I get the following error message:

Major Error 0x80040E2F, Minor Error 25016

UPDATE myTable

SET Id=Id+2
WHERE Id>=5
A duplicate value cannot be inserted into a unique index. [ Table name =
myTable,Constraint name = PK_myTable ]

I understand that it's trying to 'move' the items into already existing
places but why. When using SQL Server it batch processes and handles this
fine. Am I doing something wrong here or is there a way around this?

Many thanks

Alan

[Alberto Silva, MVP]

Hi,
I would do a cycle in reverse order to update the IDs fro mthe greatest to
the shorter. Try using an updatable SqlCeResulset to do it.

Alberto Silva

Post by Alan Lambert
I'm using SQLCE 3.5
I have a table that is storing an order list with 2 fields ID and
Description. ID is sequentailly numbered.
I want to insert 2 extra items in the middle of the list (before item 5,
UPDATE myTable
SET Id=Id+2
WHERE Id>=5
To move all items greater than 5 'up' 2 places so I can then insert the
Major Error 0x80040E2F, Minor Error 25016

Post by Alan Lambert
UPDATE myTable

SET Id=Id+2
WHERE Id>=5
A duplicate value cannot be inserted into a unique index. [ Table name =
myTable,Constraint name = PK_myTable ]
I understand that it's trying to 'move' the items into already existing
places but why. When using SQL Server it batch processes and handles this
fine. Am I doing something wrong here or is there a way around this?
Many thanks
Alan
__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4713 (20091223) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4713 (20091223) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

[Jin]

It's fairly obvious that doing this can result in duplicate ID values,
thus the unique constraint error you're getting. If you can afford
to, you can do this in two-step process.

Step 1:

UPDATE myTable
SET Id=Id+2+999999
WHERE Id>=5 and Id < 999999

Step 2:

UPDATE myTable
SET Id=Id - 999999
WHERE Id >= 999999

This assumes that your ID value can never reach 999999.
If this is not an option, then what Alberto Silva suggested should be
the approach used.

- Jin