Delete Data from One Table using multiple tables in SQL Server

Question

I am trying to delete some data from one table using multiple table. The problem i am having with this query is that it is deleting data that is not suppose to delete. I want to delete only the Data where ID's in both tables are DIFFERENT. In other words, i want to keep when the ID's in both tables are the same. Here is my query:

delete  Tabel1
from Table1 r join
Table2 w on r.ID <> w.ID
and w.Date_Assigned is not null

Cristian Lupascu · Accepted Answer · 2013-01-01 20:23:29Z

3

You probably want to do:

delete Tabel1 where Id not in (select Id from Table2)

Your statement will probably delete all records from Tabel1, because for every record it will find at least one in Tabel2 with a different ID.

To test this, just run

select r.ID
from Table1 r join
Table2 w on r.ID <> w.ID
and w.Date_Assigned is not null

and you'll understand what the problem is.

answered Jan 1, 2013 at 20:23

Cristian Lupascu

40.8k18 gold badges107 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete Data from One Table using multiple tables in SQL Server

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related