Search code examples
sqlsql-delete

SQL DELETE with 2 Joins?

I have to delete a table, which has disabled users in it. The problem is, that I have to use the where clause in the end. This is the most important point, cause otherwise i can't test my query.

The where clause is important, cause without it I would delete the whole database, not only the disabled users.

DELETE a
   FROM CONTENT_PERM a
   JOIN user_mapping b
     ON a.USERNAME = b.USER_KEY
        (JOIN CWD_USER c
           ON b.USERNAME = c.USER_NAME)
  WHERE c.ACTIVE = 'F';
Solution

  • You can try below

    DELETE FROM CONTENT_PERM
WHERE USERNAME IN (SELECT USER_KEY FROM user_mapping WHERE USERNAME IN (SELECT USER_NAME
from CWD_USER where ACTIVE = 'F'));