sql - The replace statement in a trigger on sqlite3 -

first created 2 tables:

create table min_data( id integer primary key, ic char(5) not null, dt datetime not null, cou float, max float not null, avg float not null, min float not null);  create table min_data( id integer primary key, ic char(5) not null, dt datetime not null, cou float, max float not null, avg float not null, min float not null); create unique index in_ic_dt on hour_data(ic, dt); 

then created trigger follows.

create trigger ins after insert on min_data begin     replace hour_data(ic, dt, cou, max, avg, min)      select ic, strftime('%y-%m-%d %h:00:00', dt), avg(cou) * 6, max(max), avg(avg), min(min) min_data strftime('%y-%m-%d %h:00:00', new.dt) <= dt , dt < strftime('%y-%m-%d %h:00:00', new.dt, '+1 hour') , ic = new.ic; end; 

here problem. after inserted records min_data, trigger insert records hour_data, id of records in hour_data doesn't begin 1 , discrete. how can fix problem?

replace not update existing record; deletes old record, , inserts new one.

you have update or insert hand:

create trigger ... begin     -- update old record, if exists:     update hour_data     set cou = (select avg(cou) * 6 ...),         max = (select max(max) ...),         ...     ic = new.ic       , dt = strftime('%y-%m-%d %h:00:00', new.dt);      -- insert new record,     insert hour_data(...)     select ...     ...       -- if not yet exist:       , not exists (select 1                       hour_data                       ic = new.ic                         , dt = strftime('%y-%m-%d %h:00:00', new.dt)); end;