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
