Re: [sqlite] about delete problem

[Hick Gunter]

There is no easy and efficient way to do this. Autoincrement needs to store 
only the last value used (or the next value to use). 63 bits of unique IDs 
should be enough.

You may try the following, but it is probably rather expensive.

INSERT INTO mytable (SELECT max(id)+1, FROM mytable);

Also, what should the behavior be if you delete a "middle" id? Should it be 
reused? If so, when? Why do you require ids to be contiguous? Are you trying to 
adapt an algorithm based on an in-memory array?

-Ursprüngliche Nachricht-
Von: YAN HONG YE [mailto:yanhong...@mpsa.com]
Gesendet: Mittwoch, 16. Jänner 2013 09:17
An: sqlite-users@sqlite.org
Betreff: [sqlite] about delete problem

create table mytable( ID integer primary key autoincrement, name nvarchar(32) 
); insert into mytable(name) values("aa1"); insert into mytable(name) 
values("aa2"); insert into mytable(name) values("aa3"); insert into 
mytable(name) values("aa4"); insert into mytable(name) values("aa5"); insert 
into mytable(name) values("aa6"); delete from mytable where id>3; insert into 
mytable(name) values("aa7"); insert into mytable(name) values("aa8"); select * 
from mytable;
1|aa1
2|aa2
3|aa3
7|aa7
8|aa8

I wish after I  delete any rows from mytable, and then when I insert into any 
row into mytable, The Id should follow the last exists in mytable.
in the sample sql, when I delete any rows in mytable,the last id is 3 ,  and 
then when I add any column into mytable, the ID should from 3 to the next 
column, how to do this? maybe have any rubbish in the database when delete from 
mytable, how to clean that rubbish?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] about delete problem

[Simon Davies]

On 16 January 2013 08:16, YAN HONG YE  wrote:
> create table mytable( ID integer primary key autoincrement, name nvarchar(32) 
> );
> insert into mytable(name) values("aa1");
> insert into mytable(name) values("aa2");
> insert into mytable(name) values("aa3");
> insert into mytable(name) values("aa4");
> insert into mytable(name) values("aa5");
> insert into mytable(name) values("aa6");
> delete from mytable where id>3;
> insert into mytable(name) values("aa7");
> insert into mytable(name) values("aa8");
> select * from mytable;
> 1|aa1
> 2|aa2
> 3|aa3
> 7|aa7
> 8|aa8
>
> I wish after I  delete any rows from mytable, and then when I insert into any 
> row into mytable, The Id should follow the last exists in mytable.
> in the sample sql, when I delete any rows in mytable,the last id is 3 ,  and 
> then when I add any column into mytable, the ID should from 3 to
> the next column, how to do this? maybe have any rubbish in the database when 
> delete from mytable, how to clean that rubbish?

Try:

create table mytable( ID integer primary key, name nvarchar(32) );

and read up on "integer primary key" and "autoincrement" on the SQLite web-site

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] about delete problem

[YAN HONG YE]

create table mytable( ID integer primary key autoincrement, name nvarchar(32) );
insert into mytable(name) values("aa1");
insert into mytable(name) values("aa2");
insert into mytable(name) values("aa3");
insert into mytable(name) values("aa4");
insert into mytable(name) values("aa5");
insert into mytable(name) values("aa6");
delete from mytable where id>3;
insert into mytable(name) values("aa7");
insert into mytable(name) values("aa8");
select * from mytable;
1|aa1
2|aa2
3|aa3
7|aa7
8|aa8

I wish after I  delete any rows from mytable, and then when I insert into any 
row into mytable, The Id should follow the last exists in mytable.
in the sample sql, when I delete any rows in mytable,the last id is 3 ,  and 
then when I add any column into mytable, the ID should from 3 to 
the next column, how to do this? maybe have any rubbish in the database when 
delete from mytable, how to clean that rubbish?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users