On 12.03.2012 16:02, Christian Smith wrote:
I've had a similar problem in the past, and solved it by using a pre-insert
trigger to do the desired update.
Thus, the insert/update from above becomes just an insert (or ignore),
with an implied update in the trigger, which appears to be sematically
closer to what people want in the above case (though not in the original
subject matter.)
Thank you Christian!
Your insightful advice led me to the following (slightly more natural)
variation (with the OP's sample):
create view t1_inc as
select
t1.rowid, t1.a, t2.b,
t1.a + t2.b a_next, t1.b + t2.b b_next
from t2
inner join t1 on t1.id = t2.id
;
create trigger t1_inc_apply instead of update on t1_inc begin
update t1
set a = NEW.a_next, b = NEW.b_next
where rowid = NEW.rowid
;
end
;
update t1_inc set a = a_next, b = b_next
;
The only visible downside is, that in both variants (your original/the
above), generated VDBE code contains OpenEphemeral and (AFAICT)
temporary record for every row in the join, but maybe someone knows
variation of the trigger based approach which avoids this ... ?
Kind regards,
Alek
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
