sql - SQLite renumber ID using cycle -

hello have table many inserted row. need renumber row id , order them.

i have found code not work me.

set @i = 100; update "main"."categories" set id = (@i := @i +1) "name" = "white"; alter table "main"."categories" auto_increment = 1 

so using code above expected renumbered records have name - white , start insert them 100 increment 1. not work me. maybe there problem in code maybe difference between sql , sqlite query.

this how created table:

create table categories (id integer primary key, name text, free numeric) 

i hope there made solution how because don't want manually :)

that code not standard sql.

sqlite not have many programming constructs because designed embedded database more natural have logic in host language.

if want in sql, try following:
first, create temporary table have autoincrement column can used counting:

create temporary table new_ids(i integer primary key, old_id integer); 

insert dummy record ensure next new record starts @ 100, insert ids of categories table want change:

insert new_ids values(99, null); insert new_ids select null, id "categories" "name" = 'white'; delete new_ids = 99; 

then can change these ids in original table:

update "categories"   set id = (select new_ids old_id = "categories".id)   id in (select old_id new_ids); drop table new_ids;