Thanks. Have a feeling I made this same mistake before and posted to this forum as well ...
RBS On Wed, Oct 17, 2012 at 4:13 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > Bart Smissaert <bart.smissa...@gmail.com> wrote: >> To do with the same, what is wrong with this update SQL? >> >> update final2 >> set group_count = >> (select count(*) >> from >> final2 >> group by >> group_marker) >> >> It makes group_count always 1 > > The subquery does not depend on the values in the row being updated. It > always produces the same resultset. Now, the value of the expression of the > form "(select ...)" is the value of the first column of the first row of the > resultset. In your case, it just happens to be 1. > > You are probably looking for something like this: > > update final2 set group_count = > (select count(*) from final2 t2 where t2.group_marker = final2.group_marker); > > Here, the condition of the subquery mentions a value from the outer table, so > it's evaluated anew for every row being updated. See also: > > http://en.wikipedia.org/wiki/Correlated_subquery > > -- > Igor Tandetnik > > _______________________________________________ > 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
