Problem with SQL Server Compact Edition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Problem with SQL Server Compact Edition

Author

Topic

alanlambert
Starting Member

26 Posts

Posted - 2009-12-22 : 13:29:48

Sorry if this is a bit off-topic but I wasn't sure where to put this.

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 and is also the primary key.

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 the full 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


CREATE TABLE myTable
(
	Id          int           NOT NULL,
	Description nvarchar(150) NULL

	CONSTRAINT PK_myTable PRIMARY KEY
	(
		RouteStopId
	)
)
GO

INSERT INTO myTable(Id, Description)
SELECT 1, 'Description 1' UNION
SELECT 2, 'Description 2' UNION
SELECT 3, 'Description 3' UNION
SELECT 4, 'Description 4' UNION
SELECT 5, 'Description 5' UNION
SELECT 6, 'Description 6' UNION
SELECT 7, 'Description 7' UNION
SELECT 8, 'Description 8' UNION
SELECT 9, 'Description 9' UNION
SELECT 10, 'Description 10'

Alan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources