Delete Records from table on the basis of inner join-CodePudding

I have two tables in oracle DB called collection and collection_h. I have to delete all the records from collection_h which has the same below fields in the collection table.

I have to delete all the records from collection_h table that comes out as a result of the below query:

select * from collection inner join collection_h on
collection.pos_protocol_id  = collection_h.pos_protocol_id and
collection.terminal_pos_number = collection_h.terminal_pos_number and
collection.cb_file_number = collection_h.cb_file_number and
collection.cb_block_number = collection_h.cb_block_number and
collection.is_stan_batch = collection_h.is_stan_batch and
collection.is_transaction_date = collection_h.is_transaction_date and
collection.is_stan_trans = collection_h.is_stan_trans;

CodePudding user response：

Delete where exists

delete
from collection as c
where exists (
  select 1
  from collection_h as h
  where h.pos_protocol_id  = c.pos_protocol_id
    and h.terminal_pos_number = c.terminal_pos_number 
    and h.cb_file_number = c.cb_file_number 
    and h.cb_block_number = c.cb_block_number 
    and h.is_stan_batch = c.is_stan_batch  
    and h.is_transaction_date = c.is_transaction_date
    and h.is_stan_trans = c.is_stan_trans
);

CodePudding user response：

Please check my attached excel file here

I use these kind of excel formulas to avoid errors .