delete rows from a table that are not present in another

Question

I have two tables

table1- --- file_id ,est_edit_id,cal_head_code,cal_spec_code,cal_item_head_code

table 2-- file_id ,est_edit_id,cal_head_code,cal_spec_code,cal_item_head_code,cal_item_code

i want to delete rows from table 2 that does not match values( file_id ,est_edit_id,cal_head_code,cal_spec_code,cal_item_head_code) in table 1

any help on the same

eg
table A contains

-----------
file_id |est_edit_id,| cal_head_code| cal_spec_code| cal_item_head_code     
  1     |      2     |      3        |     4        |         5
--

table B contains

file_id   |est_edit_id ,| cal_head_code  | cal_spec_code  |   cal_item_head_code  |cal_item_code   
  1     |      2     |      3        |     4        |         5          | 20
  1     |      2     |      3        |     4        |         5          | 50
  7     |      8     |      9        |     10       |         11         |21

i want to delete row containg value 7 | 8 | 9 | 10 | 11 from table B because 7 | 8 | 9 | 10 | 11 is not present in table A

Answer 1

This should do it:

delete from table2 t2
where not exists (select 1
                  from table1 t1
                  where t1.file_id = t2.file_id
                    and t1.est_edit_id = t2.est_edit_id
                    and t1.cal_head_code = t2.cal_head_code
                    and t1.cal_spec_code = t2.cal_spec_code);

This assumes that none of the columns contain null values.

SQLFiddle example: http://sqlfiddle.com/#!15/e9e9b/1