On Tue, 09 Dec 2014 10:46:23 -0500
Igor Tandetnik <[email protected]> wrote:
> On 12/9/2014 10:38 AM, James K. Lowden wrote:
> > If the subquery to the right of the SET clause produces
> > more than one row, the statement fails.
>
> Are you sure? Normally, a scalar subquery doesn't fail when the
> resultset contains more than one row - it just silently produces the
> value from the first row of the first column.
Well, I *was* sure. I don't know about "normally", but you're right
that SQLite gets it wrong, see below. I'm pretty sure the standard
calls for a diagnostic anywhere a scalar is required and not provided.
There is a workaround worth knowing: if you add,
group by k having count(*) = 1
to the UPDATE statement below, it works correctly in the sense that
it becomes deterministic. A separate check is required of course to
determine if there were any count(*) > 1.
[snip]
create table T ( k int primary key, v string );
create table S ( k int, v string, primary key( k,v) );
insert into T values (1, 'a'), (2, 'b');
insert into S values (1, 'y'), (1, 'z');
select * from T;
k v
---------- ----------
1 a
2 b
select * from S;
k v
---------- ----------
1 y
1 z
select * from T join S on T.k = S.k;
k v k v
---------- ---------- ---------- ----------
1 a 1 y
1 a 1 z
update T
set v = (select v from S where k = T.k)
where exists (
select 1
from S where k = T.k
);
select * from T;
k v
---------- ----------
1 y
2 b
[pins]
--jkl
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
