On 19-10-2014 17:48, Keith Medcalf wrote:
for a table test(i integer primary key, j integer) the new i (rowid) is as follows: test.i = case when test.i is not null then test.i else case max(test.i) when null then 1 else max(test.i)+1 end end if you add the autoincrement keyword, then the algorithm becomes test.i = case when test.i is not null then test.i else case when sequence[test.i] is null then 1 else sequence[test.i]+1 end end sequence[test.i] = case when sequence[test.i] is null or test.i > sequence[test.i] then test.i else sequence[test.i] end and the update of sequence[test.i] occurs whether or not the transaction commits.
thanks for the clear answer i could not find it when looking at these links: https://www.sqlite.org/autoinc.html http://www.sqlite.org/faq.html _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users