How to delete records from a table when not found in another table?

Question

I have temp table with data #temp_table

I have a 2nd table with data - final_table

I want to delete records from final_table , those records that are not in the #temptable, for that projectID only. Meaning I need to delete this record from the final__table.

End result on the final_table should be -

I tried a couple of delete stmts, but they all end up deleting that one record for that projectID and also the other records for other projectIDs too.. Any help from anyone is highly appreciated. Thank you folks.

Answer 1

join on project id, then delete missing.

delete 
from final_table f
join #temp_table t on f.project_id = t.project_id
where not exits (select * 
                  from #temp_table t2
                  where t2.project_id = f.project_id
                        and t2.pipeline_id = f.pipline_id)

or just a where clause:

delete 
from final_table f
where exits (select * 
                  from #temp_table t
                  where t.project_id = f.project_id)
and not exits (select * 
                  from #temp_table t
                  where t.project_id = f.project_id
                        and t.pipeline_id = f.pipline_id)

Answer 2

You can use a DELETE statement with a subquery to achieve this. Here's an example:

DELETE FROM final_table
WHERE projectID = 'ABC'
AND NOT EXISTS (
    SELECT 1
    FROM #temp_table
    WHERE #temp_table.userID = final_table.userID
)

This statement deletes all records from final_table for the specified projectID where the userID does not exist in #temp_table. The NOT EXISTS subquery checks if a matching userID exists in #temp_table. Note that you should replace 'ABC' with the actual projectID you want to delete records for. If you want to delete records for multiple **projectID**s, you can include them in a WHERE clause with an IN operator, like this:

DELETE FROM final_table
WHERE projectID IN ('ABC', 'DEF')
AND NOT EXISTS (
    SELECT 1
    FROM #temp_table
    WHERE #temp_table.userID = final_table.userID
)

This deletes records for both ABC and DEF **projectID**s that are not in #temp_table.