Keith, thanks for this. The statement Igor made is what I need. gert
2013/11/2 Keith Medcalf <kmedc...@dessus.com> > > Can you write a SELECT which returns the data that you want? > Can you write a WHERE clause which selects the records you wish to update? > > For example: > > update T > set V = (select avg(t2.g) > from t as t2 > where t2.g=t.g) > where V IS NULL; > > assuming you only want to update the value of V in rows where V IS NULL ... > > >-----Original Message----- > >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > >boun...@sqlite.org] On Behalf Of Gert Van Assche > >Sent: Saturday, 2 November, 2013 11:07 > >To: sqlite-users > >Subject: [sqlite] Update and GROUP BY > > > >All, I have this table: > > > >DROP TABLE T; > >CREATE TABLE T (N, V, G); > >INSERT INTO T VALUES('a', 1, 'x'); > >INSERT INTO T VALUES('b', 3, 'x'); > >INSERT INTO T VALUES('c', null, 'x'); > >INSERT INTO T VALUES('d', 80, 'y'); > >INSERT INTO T VALUES('e', null, 'y'); > >INSERT INTO T VALUES('f', 60, 'y'); > >INSERT INTO T VALUES('g', null, 'y'); > >INSERT INTO T VALUES('h', null, 'z'); > >INSERT INTO T VALUES('i', 111, 'z'); > > > >I would like to see where N='c', V as the average for the group (G) were > >this record belongs to (so 'x'). > > > >Thus where N='c' I would get 2, and where N='e' or 'g', it would be 70, > >and > >where N=h it would be 111. > > > >I have no clue how to write this UPDATE statement. Could someone help me > >out? > > > >thanks > > > > > >Gert > >_______________________________________________ > >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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
