CODEWITHSUNDEEP
sqloracle-database
David Faizulaev
David Faizulaev

Reputation: 5761

SQL - Delete selected row/s from database

I'm quite new to SQL and I'm having issues with deleting a selected row/s from a table.

I've written a query that selects the desired rows from the table, but when I try to execute DELETE FROM table_name WHERE EXISTS it deletes all the rows in the database.

Here is my complete query:

DELETE FROM USR_PREF WHERE EXISTS (
  SELECT *
    FROM USR_PREF
    WHERE USR_PREF.USR_ID = 1
      AND ((USR_PREF.SRV NOT IN (SELECT SEC_ENTITY_FOR_USR_ACTION_VIEW.ENTITYT_ID
          FROM SEC_ENTITY_FOR_USR_ACTION_VIEW
          WHERE SEC_ENTITY_FOR_USR_ACTION_VIEW.USR_ID = 1
            AND SEC_ENTITY_FOR_USR_ACTION_VIEW.ENTITYTYP_CODE = 2
            AND USR_PREF.DEVICE IS NULL)

      OR (USR_PREF.DEVICE NOT IN (SELECT SEC_ENTITY_FOR_USR_ACTION_VIEW.ENTITYT_ID
          FROM SEC_ENTITY_FOR_USR_ACTION_VIEW
          WHERE SEC_ENTITY_FOR_USR_ACTION_VIEW.USR_ID = 1
            AND SEC_ENTITY_FOR_USR_ACTION_VIEW.ENTITYTYP_CODE = 3)))))

The select query returns the desired rows, but the DELETE command just deletes that entire table.

Please assist.

Upvotes: 0

Views: 949

Answers (1)

JNevill
JNevill

Reputation: 50273

Your where clause WHERE EXISTS (SOME QUERY) is the problem here. You are basically saying "Delete everything if this subquery returns even one result".

You need to be more explicit. Perhaps something like:

DELETE FROM USR_PREF
WHERE USR_FIELD IN (
    SELECT USR_FIELD
    FROM USR_PREF
    WHERE USR_PREF_T.USER_ID=1
        AND ((USR_PREF.SRV NOT IN ...

and so on... With this, only records that match records returned in your subquery will be deleted.

Upvotes: 1

Related Questions