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
