0

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.

Zephyr
  • 9,885
  • 4
  • 28
  • 63
  • check out this example https://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server – Jeremy Jan 08 '21 at 01:54
  • This form of subquery could work, although a join is easier. But the issue with this query is that `user_id` is not a column in `users`. If you alias the tables you will see what I mean `SELECT u.user_id FROM users u` – Charlieface Jan 08 '21 at 02:30

1 Answers1

2

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'
jcHernande2
  • 301
  • 2
  • 6