This construct does not work in postgresql 9.3.5 (unless I have a typo).
However, I would love for it to work in both Postgresql and Sqlite.
djenkins@ostara ~ $ psql -Upostgres -dcapybara_regtest
psql (9.3.5)
Type "help" for help.
capybara_regtest=# create table test1 (col1 integer, col2 integer, col3
text);
CREATE TABLE
capybara_regtest=# insert into test1 values (1, 2, 'hello');
INSERT 0 1
capybara_regtest=# update test1 set (col1, col2, col3) = (select 4, 5,
'bye');
ERROR: syntax error at or near "select"
LINE 1: update test1 set (col1, col2, col3) = (select 4, 5, 'bye');
^
capybara_regtest=# \q
On Mon, Sep 15, 2014 at 3:51 AM, Mark Lawrence <no...@null.net> wrote:
> I occasionally have the need to update two columns based on complex sub
> queries, which are often very similar
>
> UPDATE
> t
> SET
> x = ( SELECT 1...),
> y = ( SELECT 2...) -- nearly the same as SELECT 1
> ;
>
> 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
> ;
>
> However CTEs don't work within triggers.
>
> I was wondering hard it would be to support the SET syntax as shown in
> the subject line. I believe something like that works in PostgreSQL and
> I could use it in SQLite for performance reasons.
>
> UPDATE
> t
> SET
> (x,y) = (SELECT 1,2)
> ;
>
> Alternatively, is there any effort underway to make CTEs work inside
> triggers?
>
> --
> Mark Lawrence
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
