Search code examples
sqlmariadbinnodb

Table 'db_session' is specified twice, both as a target for 'DELETE' and as a separate source for data

trying to run this sql query but i'm getting the error above:

DELETE FROM db_session
where time NOT IN (SELECT MAX(time) FROM db_session GROUP BY username)

SELECT function though works as expected.

Solution

  • The only database that causes this problem (as far as I know) is MySQL (and related databases). You can replace the logic with a JOIN:

    delete s
    from db_session s join
         (select username, max(time) as maxtime
          from db_session s2
          group by username
         ) ss
         on s.username = ss.username and s.time < ss.maxtime;

    An added bonus is that the logic is also correct. Your logic deletes any rows where the time is not the maximum time for any username. However, if it could keep multiple rows for a user name if the different rows happen to be the maximum time for some other user.