Changing a value in a column to another from the same column-CodePudding

I have a table named titles with 3 columns named titleID, price, and pubDate

I've been trying to change the price of a row to the same price as the row with the most recent pubDate

I have

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

The error I am getting is

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

How can I correctly change a value to a value from the same column

CodePudding user response：

One workaround to this error is to use an update join:

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;

CodePudding user response：

Another solution is to add an outer query in the select statement:

UPDATE titles t
INNER JOIN titles t2
ON t.titleID = t2.titleID
SET
    t.price = ( SELECT t1.price FROM (
                                     SELECT t3.price 
                                     FROM titles t3
                                     ORDER BY t.pubDate DESC LIMIT 1) as t1 )
WHERE t.titleID = 1001

For more details check: https://dev.mysql.com/doc/refman/8.0/en/update.html