MySQL Update with Subquery
I've got an annoying issue with an update query I'm trying to get
working... The following statement SHOULD update channels.media_view_count
to the result of the subquery (for all channels).
UPDATE
channels c
SET
c.media_view_count = (
SELECT
SUM(t.view_count)
FROM (
SELECT DISTINCT
m.viewkey,
m.view_count
FROM
media m
INNER JOIN
participants p
ON
m.id = p.medium_id
WHERE
p.user_id = c.id
AND
m.is_viewable = 1
AND
(p.pending = 0)
) AS t
);
The subquery works fine independently (when specifying an actual id for
c.id, like 47778 or whatever), but when I execute this statement, I get:
ERROR 1054 (42S22): Unknown column 'c.id' in 'where clause'
I thought I would be able to access the channels table (aliased as c) from
within the subquery? Am I missing something or am I totally wrong here?
Any and all help is appreciated :)
Thanks,
Jeff
No comments:
Post a Comment