scottlittle scottlittle - 1 year ago 74
SQL Question

Delete intersection between two tables in SQL Server

I wanted to delete the matching accounts (matching on ID and acct_num) from #temp_scoring, but ended up deleting all rows from the table #temp_scoring instead. Here is the code:

delete from #temp_scoring
where exists (
select * from #temp_edu te
where te.ID = ID
and te.acct_num = acct_num)

As a side note, I would have created an alias for #temp_scoring but it was giving me a syntax error when I did that.

Answer Source
    #temp_scoring s
    INNER JOIN #temp_edu te
    ON s.ID = te.ID
    AND s.acct_num = te.acct_num

You can just do a delete with join