Обновить запрос с помощью подвыборов из той же таблицы

Question

Я пытаюсь создать запрос для обновления rank поле всех записей в таблице, основанных на значениях из той же таблицы.

Мне удалось получить работу SELECT запрос, который вычисляет ранг, но мне трудно преобразовать его в UPDATE, Это запрос:

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

Как я могу имплантировать его в UPDATE запрос, чтобы обновить каждую запись rank значение?

Спасибо!:)

РЕДАКТИРОВАТЬ:

Моя структура БД:

[Photo]
id
user_id
views_count
rank
createdAt

[Comment]
id
photo_id
content

Answer 1

Просто предположение, но что-то вроде этого должно работать:

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

Answer 2

Вы должны записать свои новые ранги во временную таблицу и использовать ее для обновления исходной таблицы. пытаться

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);

а потом

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

и наконец

DROP TABLE tmp_tbl;

Посмотрите этот вопрос simliar stackru