Mysql cross table update permission problem

Question

I'm doing a MySQL query similar to following:

    UPDATE my_articles a
LEFT JOIN categorylinks cl ON a.pageid = cl.cl_from
      SET a.importance = 'High'
    WHERE cl.cl_to = 'High'

The problem is, I don't have a UPDATE right for the categorylinks table (I do have that right for my_articles), so the query fails with the message

 UPDATE command denied to user 'svick'@'willow.toolserver.org' for table 'categorylinks'

How can I work around this?

Answer 1

I would do it with Select inside Update.

Something like that (not tested):

UPDATE my_article a
SET a.importance = 'High'
WHERE a.page_id IN(SELECT cl.cl_from FROM categorylinks cl WHERE cl.cl_to='High')