sql server - SQL: Delete Rows from Dynamic list of tables where ID is null

Question

I'm a SQL novice, and usually figure things out via Google and SO, but I can't wrap my head around the SQL required for this.

My question is similar to Delete sql rows where IDs do not have a match from another table, but in my case I have a middle table that I have to query, so here's the scenario:

We have this INSTANCES table that basically lists all the occurrences of files sent to the database, but have to join with CROSS_REF so our reporting application knows which table to query for the report, and we just have orphaned INSTANCES rows I want to clean out. Each DETAIL table contains different fields from the other ones.

I want to delete all single records from INSTANCES if there are no records for that Instance ID in any DETAIL table. The DETAIL table got regularly cleaned of old files, but the Instance record wasn't cleaned up, so we have a lot of INSTANCE records that don't have any associated DETAIL data. The thing is, I have to select the Table Name from CROSS_REF to know which DETAIL_X table to look up the Instance ID.

In the below example then, since DETAIL_1 doesn't have a record with Instance ID = 1001, I want to delete the 1001 record from INSTANCES.

INSTANCES

Instance ID	Detail ID
1000	123
1001	123
1002	234

Answer 1

Storing table names or column names in a database is almost always a sign for a bad database design. You may want to change this and thus get rid of this problem.

However, when knowing the possible table names, the task is not too difficult.

delete from instances i
where not exists
(
  select null
  from cross_ref cr
  left join detail_1 d1 on d1.instance_id = i.instance_id and cr.table_name = 'DETAIL_1'
  left join detail_2 d2 on d2.instance_id = i.instance_id and cr.table_name = 'DETAIL_2'
  left join detail_3 d3 on d3.instance_id = i.instance_id and cr.table_name = 'DETAIL_3'
  where cr.detail_id = i.detail_id
  and 
  (
    d1.instance_id is not null or
    d2.instance_id is not null or
    d3.instance_id is not null    
  )
);

(You can replace is not null by = i.instance_id, if you find that more readable. In that case you could even remove these criteria from the ON clauses.)