Search code examples
sqloracle-databasehaving

Oracle SQL to delete table entries using having clause

I have a SQL query which finds userids in a table based on last login date

select USER_ID, max(login_time) maxd from MyTable group by User_Id having max(login_time) < (sysdate - 90)

I need to delete the entries that this query finds

I have tried

DELETE a
FROM   MyTable a
JOIN
(
    select USER_ID, max(login_time) maxd from MyTable group by User_Id having max(login_time) < (sysdate - 90)
) b ON a.USER_ID = b.USER_ID

but this results in SQL Error: ORA-00933: SQL command not properly ended

Can anyone help ?

Solution

  • Oracle supports delete join syntax, but it is a bit different than other databases.

    DELETE FROM (
    SELECT a.*
    FROM MyTable a
    INNER JOIN
    (
        SELECT USER_ID
        FROM MyTable
        GROUP BY User_Id
        HAVING MAX(login_time) < (sysdate - 90)
    ) b ON a.USER_ID = b.USER_ID
);