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,<new_value> 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
