Hi members,
I found the the restriction of updating unique column in ver3.6.21.
and same problem is reported in follwoing mail
Marc-Andre Gosselin wrote:
date: Thu, 16 Jun 2005
title: "[sqlite] Update unique column"
>
> I discovered a behavior in SQLite 2.8.16 that doesn't conform to the SQL
> standard, here's an example :
>
> CREATE TABLE tbUpdateUnique (a INTEGER PRIMARY KEY, b INTEGER UNIQUE, c
> VARCHAR(100));
> INSERT INTO tbUpdateUnique VALUES('', 1, "Test 1");
> INSERT INTO tbUpdateUnique VALUES('', 2, "Test 2");
> INSERT INTO tbUpdateUnique VALUES('', 3, "Test 3");
>
> Now when I try the following update, I get a constraint error :
>
> UPDATE tbUpdateUnique SET b = b + 1 WHERE b >= 2;
>
> In the SQL Standard and NIST SQL test suite they say than an update
> should be considered atomic, and verify unique constraints only after the
> operation has
> updated all rows. From what I experienced with SQLite, constraints are
> verified
> after each row has been updated, resulting in a constraint error. I also tried
> these with no success :
>
> BEGIN TRANSACTION;
> UPDATE tbUpdateUnique SET b = b + 1 WHERE b >= 2;
> COMMIT TRANSACTION;
>
> and
>
> UPDATE tbUpdateUnique SET b = b + 1 WHERE a IN (SELECT a FROM tbUpdateUnique
> WHERE b >= 2 ORDER BY b DESC);
>
# full mail can be seen in archieve log at
# http://www.mail-archive.com/[email protected]/msg08597.html
Will this restriction be fixed in near feature?
I also know the workaround to use temporary value and update twice.
(example is shown is
http://www.mail-archive.com/[email protected]/msg50894.html)
but I don't want to use this workaround if possible
because it is not gut feeling and less performance.
Regards,
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
