On Tue, 09 Dec 2014 10:46:23 -0500 Igor Tandetnik <i...@tandetnik.org> 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users