I'm trying to use the UPDATE statement to modify several columns of a
table with a single subquery. Here's an example:
create table xy(i, x,y);
create table uv(i, u,v);
insert into xy('i', 'x', 'y') values (8, 1.0, 2.0);
insert into xy('i', 'x', 'y') values (9, 1.1, 2.1);
insert into uv('i', 'u', 'v') values (8, 1.3, 2.3);
The next statement is what I'd like to be able to do but is rejected
with a syntax error:
update xy set ('x', 'y') = (select u, v from uv,xy where xy.i = uv.i);
The command line sqlite tool version 2.8.13 (on a Debian Linux box) rejects
this line with
SQL error: near "(": syntax error
This statement gives me what I want:
update xy set x = (select u from uv,xy where xy.i = uv.i),
y = (select v from uv,xy where xy.i = uv.i);
but it appears inefficient in that it has to do the same join twice. It
will be worse in my actual database because I'm trying to update three
columns at once. Is there a way to write the UPDATE so that the join
is done just once? I'd like to avoid making a temp table if possible.
-- Al
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
