update query with subselects from the same table

Question

I'm trying to create a query to update the rank field of all the records in the table, based on values from the same table.

I managed to get a working SELECT query that calculates the rank, but I'm having a hard time converting it to an UPDATE. This is the query:

SELECT
  ((views_count + comments_count) * (172800 / elapsed)) AS rank
FROM (
  SELECT
    p.views_count,
    (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(p.createdAt)) AS elapsed,
    (SELECT COUNT(*) FROM `comments` AS c WHERE c.photo_id = p.id) AS comments_count
  FROM `photos` AS p
) AS m

How can I implant it in an UPDATE query, to update each record's rank value?

Thanks! :)

EDIT:

My DB structure:

[Photo]
id
user_id
views_count
rank
createdAt

[Comment]
id
photo_id
content

can you post the structure. This can be achieved by a join – Muhammad Raheel Apr 21 '13 at 18:29 — Muhammad Raheel, Apr 21 '13 at 18:29

score 0 · Answer 1 · answered Apr 21 '13 at 17:50

Just a guess, but something like this should work:

UPDATE `photos`
FROM (
   SELECT id
        , (views_count + comments_count) * (172800 / elapsed) AS rank
   FROM (
      SELECT p.id
           , p.views_count
           , (  UNIX_TIMESTAMP(NOW()) 
              - UNIX_TIMESTAMP(p.createdAt)) AS elapsed
           , (SELECT COUNT(*) 
              FROM `comments` AS c 
              WHERE c.photo_id = p.id) AS comments_count
      FROM `photos` AS p
      ) AS m
   ) AS z
SET rank=z.rank
WHERE `photos`.id=z.id

score -1 · Answer 2 · edited May 23 '17 at 12:18

You should write your new ranks to a temporary table and use that to update the original table. try

CREATE TEMPORARY TABLE tmp_tbl AS (
   SELECT ((views_count + comments_count) * (172800 / elapsed)) AS rank, m.id
      FROM (SELECT
         p.views_count,
         (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(p.createdAt)) AS elapsed,
         (SELECT COUNT(*) FROM `comments` AS c WHERE c.photo_id = p.id) AS comments_count
         FROM `photos` AS p
      ) AS m);

and then

UPDATE photos, tmp_tbl SET photos.rank = tmp_tbl.rank WHERE photos.id=tmp_tbl.id;

and finally

DROP TABLE tmp_tbl;

See this simliar stackoverflow question

update query with subselects from the same table

EDIT:

2 Answers2