Update table with items where the date is the maximum-CodePudding

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