On Mon Sep 15, 2014 at 10:51:04AM +0200, Mark Lawrence wrote:
>
> Normally one could use a CTE to do the work once:
>
> WITH
> cte
> AS (
> SELECT 1 AS x, 2 AS y
> )
> UPDATE
> t
> SET
> x = cte.x,
> y = cte.y
> ;
Actually this doesn't appear to work. I assumed it would based on the
documentation which says:
...common table expressions (ordinary and recursive) are created by
prepending a WITH clause in front of a SELECT, INSERT, DELETE, or
UPDATE statement.
Unfortunately there are no examples given for how a CTE works with an
UPDATE. So I tried accessing the cte using subqueries which is perhaps
how it is intended:
WITH
cte
AS (
SELECT 1 AS x, 2 AS y
)
UPDATE
t
SET
x = (SELECT x FROM cte),
y = (SELECT y FROM cte)
;
Maybe the documentation could be improved with a couple of
UPDATE/INSERT/DELETE examples?
> However CTEs don't work within triggers.
This is still my issue of course.
--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
