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 I don't think I can use WHERE IN because I need multiple elements returned by the sub-select. Any suggestions? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
