'Update table with items where the date is the maximum

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.

mysqlmax


Solution 1:[1]

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

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 sagi
Solution 2 Ergest Basha

Related Questions