Oracle SQL to delete table entries using having clause-CodePudding

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 ?

CodePudding user response：

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
);

CodePudding user response：

Use your select as a subquery:

DELETE FROM MyTable
  WHERE (user_id, login_time) IN
           -- this is your query:
           (  SELECT USER_ID, MAX (login_time) maxd
                FROM MyTable
            GROUP BY User_Id
              HAVING MAX (login_time) < (SYSDATE - 90));

CodePudding user response：

You can correlate on the ROWID pseudo-column and use an analytic function:

DELETE FROM mytable
WHERE ROWID IN (
  SELECT ROWID
  FROM   (
    SELECT login_time,
           MAX(login_time) OVER (PARTITION BY user_id) AS max_login_time
    FROM   mytable
  )
  WHERE  max_login_time < SYSDATE - INTERVAL '90' DAY
  AND    login_time = max_login_time
)