Select list of IDs from one table and delete all IDs from a second table in SQL Server

Question

I'm trying to figure out the best query to accomplish the following.

I have 2 tables that are related by an ID column.

Table1

ID
UserID

Table2

ID
SomeDataColumn

What I need to do is select a list of IDs from Table1 where my UserID='5' or some number. This should return multiple rows with respective IDs. Then I need to delete all rows from Table2 that contain any ID from my returned list of IDs from Table1.

The only way I could figure out how to do this was to select all of the rows from my first table, put them in a cursor, and then loop through each one running a Delete query for each ID. But it seems there must be a more efficient way to do this with a join or something.

Any ideas? Thanks all!

Answer 1

This might get you there:

delete from Table2 where ID in (select ID from Table1 where UserID = '5')

You're right to think there is a better way in SQL

Answer 2

3 ways

DELETE   FROM SomeTable
WHERE id IN(SELECT id FROM AnotherTAble WHERE USerID = 5 and id is not null)

DELETE  s
 FROM SomeTable s
JOIN AnotherTAble  a on s.ID = a.ID
WHERE a.USerID = 5


DELETE  s
 FROM SomeTable s
WHERE EXISTS (SELECT 1 from AnotherTAble  a WHERE a.ID = S.ID
AND a.USerID = 5)

Answer 3

 DELETE Table2
 WHERE ID IN (
     SELECT ID FROM Table1
     WHERE UserID=5
 )

That does it?

Answer 4

Use a join,

DELETE FROM [Table2] 
FROM [Table2] as t2 
INNER JOIN [Table1] as t1 ON t2.[ID] = t1.[ID] 
WHERE t1.[UserID] = 5;