To delete rows in a table that do not match a row in another table:

DELETE FROM table1 WHERE id NOT IN (SELECT id from table2);

------

To create an updatable view:

create view testview as select  *, _rowid_ as _rowid_ from test order by a

(note the extra _rowid_ column - you must say "as _rowid_")

The order by clause can be anything

for each column:

crate trigger <table><column>
  instead of update of <column> on <view>
  for each row
    begin update <table> set <column> = new.<column>
      where <table>._rowid_ = old._rowid_ ;
 end

to add rows:

create trigger <table>insert
  instead of insert on <view>
  for each row
    begin insert into <table> values(new.a,new.b,new.c); -- one for each column
    end


create table test(a,b,c)

create view testview as select  *, _rowid_ as _rowid_ from test order by a

drop trigger testa

CREATE TRIGGER testa
  instead of update of a on testview
  for each row
    begin update test set a = new.a where test._rowid_ = old._rowid_
        ;
    end


To create an updatable view:

create view testview as select *, _rowid_ as _rowid_ from test order by a

(note the extra _rowid_ column - you must say "as _rowid_")

The order by clause can be anything

Then, for each column, add a trigger like:

create trigger testa
  instead of update of a on testview
  for each row
    begin update test set a = new.a where test._rowid_ = old._rowid_;
    end

For adding rows add a trigger:

create trigger testinsert
  instead of insert on testview
  for each row
    begin insert into test values(new.a,new.b,new.c);
    end

-----styles-----

create view stylesview as
  select *,  _rowid_ as _rowid_ from styles
  order by selector, property, value

create trigger stylesselector
  instead of update of selector on stylesview
  for each row
    begin update styles set selector = new.selector
            where styles._rowid_ = old._rowid_;
    end

create trigger stylesproperty
  instead of update of property on stylesview
  for each row
    begin update styles set property = new.property
            where styles._rowid_ = old._rowid_;
    end

create trigger stylesvalue
  instead of update of value on stylesview
  for each row
    begin update styles set value = new.value
            where styles._rowid_ = old._rowid_;
    end

create trigger stylescomment
  instead of update of comment on stylesview
  for each row
    begin update styles set comment = new.comment
            where styles._rowid_ = old._rowid_;
    end

create trigger stylesinsert
  instead of insert on stylesview
  for each row
    begin insert into styles values(new.selector,new.property,new.value,new.comment);
    end

-----substitutions-----

create view substitutionsview as
  select *,  _rowid_ as _rowid_ from substitutions
  order by pageset

create trigger substitutionspageset
  instead of update of pageset on substitutionsview
  for each row
    begin update substitutions set pageset = new.pageset
            where substitutions._rowid_ = old._rowid_;
    end

create trigger substitutionssubstitutions
  instead of update of substitutions on substitutionsview
  for each row
    begin update substitutions set substitutions = new.substitutions
            where substitutions._rowid_ = old._rowid_;
    end

create trigger substitutionscomment
  instead of update of comment on substitutionsview
  for each row
    begin update substitutions set comment = new.comment
            where substitutions._rowid_ = old._rowid_;
    end

create trigger substitutionsinsert
  instead of insert on substitutionsview
  for each row
    begin insert into substitutions values(new.pageset,new.substitutions,new.comment);
    end

-----pagesets-----



-----scripts-----


-----all-----

create table views(tablename, position, column) -- done first

create table vieworder(tablename, tableorder) -- done first

drop table if exists temp.s

create temp table s as 
  select * from views, vieworder using(tablename)
  order by tablename, position

select null, tablename, group_concat(column), tableorder from s group by tablename

select null, tablename, column from views

$1 table
$2 row or rows -- rows are new.a,new.b,new.c
$3 sort order

For each table:

drop view if exists $1view

create view $1view as
  select *,  _rowid_ as _rowid_ from $1
  order by $3

create trigger $1insert
  instead of insert on $1view
  for each row
    begin insert into $1 values($2);
    end

create trigger $1delete
  instead of delete on $1view
  for each row
  begin delete from $1
          where $1._rowid_ = old._rowid_;

For each table/column:

drop trigger if exists $1$2

create trigger $1$2
  instead of update of $2 on $1view
  for each row
    begin update $1 set $2 = new.$2
            where $1._rowid_ = old._rowid_;
    end