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
  FROM (
   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.

Any suggestions?
sqlite-users mailing list

Reply via email to