MySQL I need to update only the rows in a table that have the maximum date grouped by an another column.
Table Objectversions
id, ObjectID, objectdatetime, Linktime, othercolumns
I have tried
UPDATE Objectversions
SET Linktime = 1
WHERE id IN (
SELECT id
FROM Objectversions
WHERE Objectdatetime IN (
SELECT MAX(Objectdatetime)
FROM Objectversions
GROUP BY ObjectID
)
);
I get an error You can't specify target table Objectversions for update in FROM clause.
I have looked at other solutions and cannot work out how to do this. I'll be grateful for any ideas.
CodePudding user response:
Try using join instead:
UPDATE Objectversions t
JOIN (
SELECT id
FROM Objectversions
WHERE Objectdatetime IN (
SELECT MAX(Objectdatetime)
FROM Objectversions
GROUP BY ObjectID)
) s
ON(t.id = s.id)
SET t.Linktime = 1
CodePudding user response:
Even though I prefer @sagi solution another way of doing it is wrapp the subquery in an outer query.
UPDATE Objectversions
SET Linktime = 1
WHERE id IN ( SELECT t1.id FROM (SELECT id
FROM Objectversions
WHERE Objectdatetime IN (
SELECT MAX(Objectdatetime)
FROM Objectversions
GROUP BY ObjectID
)
) as t1
);
You will find a detailed explanation in the documentation about the error You can't specify target table 'items' for update in FROM clause