mittensonmaui (10/22/2013)
Hi all I am running a query where I check medical orders on an encounter there can be 1000's of orders and if one of them matches a specific criteria we delete all of them. The common thread hear is a CSN #. I am running a CTE and marking a flag column with a 1 if the criteria is hit and 0 otherwise.My question is how do I delete this record and the other orders with the same CSN? Can I us a recursive CTE, cross apply? Not sure where to go from here any help appreciated Mahalo, Brew
You are looking for something like this (note my comments):
-- let's say we wanted to delete all csn_numbers that have at least one some_value='xxx'
DECLARE @your_table TABLE
(xxx_id int identity primary key,
csn_number int not null,
some_value varchar(20) null)
INSERT INTO @your_table VALUES
(100,'fff'),(100, 'yyy'),(100,'xxx'),(140,'abc'),(150,'xxx'),
(200,'zzz'),(200, 'abc'),(210,'xxx'),(240,'bbb'),(250,'abc');
--Here's what's in there now
SELECT * FROM @your_table;
WITH kill_list AS
(
SELECT * FROM @your_table
WHERE csn_number IN (
SELECT csn_number
FROM @your_table WHERE some_value='xxx' GROUP BY csn_number)
)
DELETE FROM kill_list;
--Here's what's in there now
SELECT * FROM @your_table
The cool thing about this technique is that, in SSMS, you can highlight run your SELECT statement first to see what will be deleted.
-- Itzik Ben-Gan 2001