SQL Server : how to delete based on another table

Question

I have a USERS table with active and inactive users and I also have another table called Leaders where team leaders are stored (so a list of users).

I want to delete those users in table Leaders that are inactive in table users.

Edit based on comments:

Users table: ID and Active
Leaders table: ID

So is the ID column of the Leaders table the foreign key reference to the Users table? — Dale K, Oct 25 '18 at 22:12

score 3 · Accepted Answer · answered Oct 25 '18 at 21:02

3

You could use an in condition:

DELETE
FROM   leaders
WHERE  id IN (SELECT id
              FROM   users
              WHERE  active = 0 -- Or however you mark inactive users
             )

answered Oct 25 '18 at 21:02

Mureinik

297,002
52
306
350

you should always use two part names here [to avoid a common error](https://stackoverflow.com/q/4594733/73226) – Martin Smith Oct 25 '18 at 21:18

score 2 · Answer 2 · answered Oct 25 '18 at 21:04

2

You can make joins in delete, similar to select:

delete ld
from leaders ld
join users us on ld.idUser = us.idUser
where us.active = 0

answered Oct 25 '18 at 21:04

Felipe Martins

184
2
12

SQL Server : how to delete based on another table

2 Answers2