CODEWITHSUNDEEP
sql-server
Zephyr
Zephyr

Reputation: 10253

How do I delete rows in one table where the ID matches another table row where a field is a certain value?

I admit the title question is convoluted; here is the situation.

I have two tables:

USERS Table
id name status
1 Monica A
2 Ross A
3 Phoebe T
4 Chandler A
5 Rachel T
6 Joey A 
PERMISSIONS Table
user_id permission_id
1 32
1 51
4 12
6 2
3 5
5 22
2 18

What I want is a way to delete all rows from the PERMISSIONS table where that user's STATUS is "T" but how would I do that?

I had tried this:

DELETE FROM permissions
WHERE user_id IN (
    SELECT id FROM users
    WHERE status = 'T'
);

However, SQL Server gives this error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Upvotes: 0

Views: 1540

Answers (1)

jcHernande2
jcHernande2

Reputation: 291

try apply join permissions and user and where status = 'T'

Example:

DELETE p
FROM permissions p
INNER JOIN users u
  ON p.user_id=u.id
WHERE u.status = 'T'

Upvotes: 2

Related Questions