DELETE first, then INSERT
It seems that replace (or insert or replace) first deletes a record (if it exists) and then inserts a (new) one.
Thus, a
rowid is not stable across a «replaced» record:
sqlite> create table t(a text primary key, b text);
sqlite> insert or replace into t (a, b) values('p', 'q') returning rowid;
1
sqlite> insert or replace into t (a, b) values('x', 'y') returning rowid;
2
sqlite> insert or replace into t (a, b) values('x', 'y') returning rowid;
3
sqlite> select rowid, t.* from t;
1|p|q
3|x|y
REPLACE with aggregate function
--
-- Compare with ../update/update_with_aggregate_function.sql
--
drop table if exists tq84_child;
drop table if exists tq84_parent;
create table tq84_parent (
id int primary key,
avg_val float,
min_val float,
max_val float
);
create table tq84_child (
id_parent int references tq84_parent,
val int
);
insert into tq84_parent (id) values(1);
insert into tq84_parent (id) values(2);
insert into tq84_child values (1, 1);
insert into tq84_child values (1, 2);
insert into tq84_child values (1, 3);
insert into tq84_child values (2, 6);
insert into tq84_child values (2, 7);
insert into tq84_child values (2, 8);
insert into tq84_child values (2, 9);
insert into tq84_child values (2, 10);
replace into tq84_parent (
id,
avg_val,
min_val,
max_val
)
select
id_parent,
avg(val),
min(val),
max(val)
from
tq84_child
group by
id_parent;
.mode columns
select * from tq84_parent;