mysql - 将列中的值更改为同一列中的另一个值

Question

我有一个名为 titles 的表，其中包含名为 titleID、price 和 pubDate 的 3 列

我一直在尝试将一行的价格更改为与具有最新 pubDate 的行相同的价格

我有

UPDATE titles t
INNER JOIN titles t2
ON t.titleID = t2.titleID
SET
    t.price = (
        /*Returns the price of the titleID with the most recent date in pubID*/
        SELECT t3.price FROM titles t3
        ORDER BY t.pubDate DESC LIMIT 1)
WHERE t.titleID = 1001

我得到的错误是

Error Code: 1053. You can't specify target table t for update in FROM clause

如何正确地将值更改为同一列中的值

Answer 1

此错误的解决方法是使用更新连接:

UPDATE titles t1
INNER JOIN
(
    SELECT titleID, MAX(pubDate) AS maxPubDate
    FROM titles
    GROUP BY titleID
) t2
    ON t2.titleID = t1.titleID AND
INNER JOIN titles t3
    ON t3.titleID = t1.titleID AND
       t3.pubDate = t2.maxPubDate
SET
    price = t3.price
WHERE
    price <> t3.price;