On 3/5/2015 11:58 PM, Igor Tandetnik wrote:
> If the latter is OK, then you can have a single trigger doing something
> like this:
>
> insert or replace into Clean(X, Y, Z)
> select
> case when new.X = ifnull(c.X, d.X) then c.X else new.X end,
> case when new.Y = ifnull(c.Y, d.Y) then c.Y else new.Y end,
> case when new.Z = ifnull(c.Z, d.Z) then c.Z else new.Z end
> from Clean c join Dirty d on (c.rowid=d.rowid and c.rowid=new.rowid);
Correction:
insert or replace into Clean(rowid, X, Y, Z)
select new.rowid,
case when new.X = ifnull(c.X, d.X) then c.X else new.X end,
...
from Clean c join Dirty d on (c.rowid=d.rowid and c.rowid=new.rowid);
It's the deliberate violation of uniqueness constraint on rowid that
makes INSERT OR REPLACE work here.
--
Igor Tandetnik
