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
SELECT pr3.zip, pr3.crrt, avg(ap.[rowid]) AS avgrowid
SELECT pr1.zip, pr1.crrt, pr1.prty, 'WI' AS 'id' FROM pool_WI AS pr1
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 =
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.
sqlite-users mailing list