Thanks, Igor. My original macro did replicate the where in the sub-select. I temporarily got rid of them in my testing because it was so messy and I didn't know how to get the update working.
I will give this a shot, and thanks for time and effort. On Tue, Nov 1, 2011 at 7:45 AM, Igor Tandetnik <[email protected]> wrote: > Don V Nielsen <[email protected]> wrote: > > I need help with a complex UPDATE. I want to update each row in a table, > > calculating an average, and then apply that value back into a column of > the > > same row. Is this possible with Sqlite? Below is code that should work > > with SqlServer; its UPDATE supports a FROM statement. > > > > UPDATE m SET rtwgt = avgrowid > > FROM seg_02_matches as m > > JOIN ( > > SELECT pr3.zip, pr3.crrt, avg(ap.[rowid]) AS avgrowid > > FROM ( > > SELECT pr1.zip, pr1.crrt, pr1.prty, 'WI' AS 'id' FROM pool_WI AS pr1 > > UNION > > SELECT pr2.zip, pr2.crrt, pr2.prty, 'NY' AS 'id' FROM pool_NY AS pr2 > > ) AS pr3 > > INNER JOIN add_priorities AS ap ON ap.prty = pr3.prty AND ap.poolid = > > pr3.id > > GROUP BY pr3.zip, pr3.crrt > > ) as sub ON m.zip = sub.zip AND m.route = sub.crrt > > UPDATE seg_02_matches SET rtwgt = ( > SELECT avg(ap.rowid) > FROM ( > SELECT zip, crrt, prty, 'WI' AS id FROM pool_WI AS pr1 > UNION ALL > SELECT zip, crrt, prty, 'NY' AS id FROM pool_NY AS pr2 > ) AS pr3 > JOIN add_priorities AS ap ON ap.prty = pr3.prty AND ap.poolid = pr3.id > WHERE seg_02_matches.zip = pr3.zip AND seg_02_matches.route = pr3.crrt > ); > > Might work faster if WHERE clause were duplicated inside the subselect. > Try it both ways: > > UPDATE seg_02_matches SET rtwgt = ( > SELECT avg(ap.rowid) > FROM ( > SELECT prty, 'WI' AS id FROM pool_WI AS pr1 > WHERE seg_02_matches.zip = pr1.zip AND seg_02_matches.route = pr1.crrt > UNION ALL > SELECT prty, 'NY' AS id FROM pool_NY AS pr2 > WHERE seg_02_matches.zip = pr2.zip AND seg_02_matches.route = pr2.crrt > ) AS pr3 > JOIN add_priorities AS ap ON ap.prty = pr3.prty AND ap.poolid = pr3.id > ); > > Might be better still if you had a single "pool" table with the explicit > id column, rather than having to manufacture it on the fly with UNION ALL. > -- > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
