Solved

Delete unwanted rows

Posted on 2013-06-15

2 solutions

235 Views

Last Modified: 2013-07-01

I have a table "TreatmentData" defined as:

create table #TreatmentData(

PatientId nvarchar(18),
TreatedWith nvarchar(30) );

The table lists patient treatments. A patient can have many rows in the table. There are six possible values for TreatedWith.

I need to delete all the patient records where the patient has "NoTreatment" for all his/her rows in the database. i.e. they have visited the clinic but not been treated.

I imagine it must be where the number of rows is equal to the count where the TreatedWith is "NoTreatment" when grouped by PatientId...

but i just can not get my head round it.

Any suggestions?

Comment

Question by:soozh

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

5 Comments

LVL 16

Assisted Solution

by:Surendra Nath

Surendra Nath earned 250 total points

ID: 392505222013-06-15

the below query might help you out

;with CTE as
(
 select PatientId,TreatedWith  from #TreatmentData group by PatientId,TreatedWith 
), CTE1 AS
(
 select PatiendID from CTE C WHERE NOT EXISTS ( select 1 FROM CTE C1 where C1.PatiendID = C.PatiendID and C1.TreatedWith  <> 'NoTreatment')
)
DELETE T
FROM #TreatmentData T
JOIN CTE1 C
ON T.PatiendID = C.PatiendID

Open in new window

LVL 5

Expert Comment

by:DOSLover

ID: 392508692013-06-15

Essentially what we are saying here is that delete the patient record if we find only 'NoTreatment' records for that patient:

Delete from TreatmentData a
 where exists 
       (Select PatientId from TreatmentData b
	     where b.PatientId = a.PatientId
		   and b.TreatedWith = 'NoTreatment')
   and NOT exists 
       (Select PatientId from TreatmentData b
	     where b.PatientId = a.PatientId
		   and b.TreatedWith <> 'NoTreatment')

Open in new window

LVL 32

Accepted Solution

by:Ephraim Wangoya

Ephraim Wangoya earned 250 total points

ID: 392511192013-06-16

Here

with cte as
(
  select *
  from TreatmentData
  where TreatedWith <> 'NoTreatment'
)

delete from TreatMentData
where not exists(select 1 
                           from cte A
                           where TreatmentData.PatientID = A.PatientID)

Open in new window

LVL 25

Expert Comment

by:chaau

ID: 392520032013-06-16

Just kidding: when you apply all the SQL statements against you treatment table that you have asked during last week, you will probably end up with a nice empty table. Ha-ha

LVL 32

Expert Comment

by:awking00

ID: 392534642013-06-17

delete from treatmentdata
where patientid in
(select patientid from treatmentdata where treatedwith = 'NoTreatment'
except
select patientid from treatmentdata where treatedwith <> 'NoTreatment')

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title	# Comments	Views	Activity
Adding SQL Server Browser in after install is complete	8	60	2017-04-24
T-SQL Query - Group By Year	3	55	2017-04-19
SQL Query Returns Records in SSMS but not Classic ASP	5	49	2017-04-21
MS Access Duplicate Data Assistance	9	33	4d

SQL Server database migration - The Detach/Attach method

Article by: Vitor

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

SQL Server database migration - The Backup & Restore method

Article by: Vitor

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

Starting up a Project Part 1

Video by: GreyCampus

Starting up a Project

How to divide/split a single image file into multiple image files

Video by: Joe

In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…