CODEWITHSUNDEEP
sqloracle-database
Johnson Gale
Johnson Gale

Reputation: 165

SQL Oracle | How to delete records from a table when they match another table?

How would I delete records from a table where they match a delete table? As in, I have a table of record keys that say what need to be deleted from my main table. How would I write a delete to say "delete anything from my main table where this field matches a field in my delete table?"

Upvotes: 4

Views: 12222

Answers (1)

Ditto
Ditto

Reputation: 3344

If it's a small delete table:

delete from TableA A
   where a.key in ( select key from deleteTable );

If it's a bigger table, you can try an EXISTs:

delete from TableA A
   where exists ( select * from deleteTable d
                     where d.key = A.key );

All this depends of course, on your indexes and sizes of tables ... etc ....

(and if it gets really big, you might want to consider another option .. ie partitioning, rebuild table, etc.)

Upvotes: 6

Related Questions