Nick Kapenkar Nick Kapenkar - 3 years ago 102
SQL Question

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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download