On Mon Feb 10, 2020 at 01:34:12AM +0100, [email protected] 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
