0

This is my table:

ori table

The filteredID is the criteria that the rows will be filtered against. However, if one row meets the criteria, I want to delete all rows with the same staffID. For example, if the filteredID is set to 50 or 88.I want to filter all the "james" rows.

So my output will be:

output table

Could not think of a elegant way to tackle this.

Frostless
  • 818
  • 1
  • 11
  • 33

3 Answers3

2

the simplest way to achieve this result use 'not in' operator in where clause

select 
    staffID, Name 
from 
    Staff 
where 
    staffID not in (select staffID from Staff where filteredID = 50) 
order by 
    staffID;
Dmitry Kolchev
  • 2,116
  • 14
  • 16
  • 1
    Beware of `NOT IN clause and NULL values` (https://stackoverflow.com/questions/129077/not-in-clause-and-null-values). – Andrei Odegov May 26 '18 at 07:04
  • good point. I assumed 1. staffID is not nullable, 2. @Frostless doesn't need really delete records. but just filter from resultset – Dmitry Kolchev May 26 '18 at 07:15
1

It's as easy as this:

declare @my_table table (
  filteredID int,
  staffID int,
  Name varchar(30)
);

insert into @my_table values
  (50, 205, 'james'),
  (88, 205, 'james'),
  (57, 16371, 'jake'),
  (55, 16371, 'jake'),
  (83, 20817, 'jane'),
  (87, 20924, 'jason'),
  (49, 21074, 'jackson'),
  (42, NULL, 'Foo'),
  (170, NULL, 'Goo');

declare @filteredID int = 50;

delete from @my_table
where staffID in (
  select staffID
  from @my_table
  where filteredID = @filteredID
);

select staffID, Name from @my_table;
Andrei Odegov
  • 2,925
  • 2
  • 15
  • 21
0

Assuming staffID is a non nullable column you can simply achieve it by a subquery

DECLARE @filteredID INT
SET @filteredID = 50 --Or another value

DELETE FROM TABLE_NAME
WHERE
(
    staffID IN (SELECT staffID FROM TABLE_NAME WHERE filteredID =@filteredId)
);

SELECT * FROM TABLE_NAME
Elham Kohestani
  • 3,013
  • 3
  • 20
  • 29