On Mon Feb 10, 2020 at 01:34:12AM +0100, no...@null.net wrote: > I suspect I have found a parsing error in SQLite 3.30.1. Given the > ... > However if I wrap it inside a trigger: > > CREATE TABLE t2(b INTEGER); > > CREATE TRIGGER t2_ai > AFTER INSERT ON t2 > FOR EACH ROW BEGIN > > INSERT INTO t1 AS original (a) VALUES(NEW.b) > ON CONFLICT DO NOTHING; > > END; > > Then SQLite fails to prepare: near "AS": syntax error [for Statement > "CREATE TRIGGER...."]
I see the same behaviour with 3.31.1. Could I ask the devs if this is likely to be fixed at some point or will remain as is? On a related note and perhaps more generally interesting, I find the UPSERT mechanism quite useful for updating specific multiple rows in a specific order: INSERT INTO table SELECT columns FROM table LEFT JOIN other_table ON join_condition WHERE where_condition ORDER BY order_condition ON CONFLICT DO UPDATE SET x,y,z = (values or some other query) Previously I have used temporary tables and complicated recursive triggers to achieve what the above appears to do. I would be very interested in hearing from those that know better if the above is reliable and functions the way I think it does. It is certainly much easier to understand and I assume more efficient. Some systems apparently support an UPDATE ... JOIN syntax but I find the SQLite UPSERT implementation more powerful because of the ORDER BY possibility. -- Mark Lawrence _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users