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

user_10485812 0
Viewed 43

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

mysql
3 Answers

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

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

user_164619520

Both of the above answers didn't give me what I was looking for but they really helped in pointing me in the right direction

Here's the code that finally changed the price, I think I was searching max date row incorrectly I think the first time on top of the other errors as well

UPDATE titles t1
INNER JOIN titles t2
ON t1.titleID = t2.titleID
SET t1.price = ( SELECT t4.price FROM (
                                        SELECT t5.price
                                        FROM titles t5
                                        WHERE t5.pubDate = ( SELECT MAX(t3.pubDate)
                                                            FROM titles t3))
                                        AS t4)
WHERE t1.titleID = 1001

Here's the code that fetches the price from the row with the max date

SELECT t5.price
FROM titles t1
WHERE t5.pubDate = ( SELECT MAX(t3.pubDate)
                    FROM titles t3 )
user_104858120
Related