Solved

# Query to delete rows

Posted on 2013-06-07
259 Views
I have a tabled defined as:

``````create table #YearlyTreatmentData(

TreatmentYear int,
EyeId nvarchar(18),
TreatedWith nvarchar(30),
TreatmentCount int );
``````

The table holds the yearly treatment details for a patients with the type of treatment as TreatedWith, and the number of times treated that year with the treatment as TreatmentCount.

EyeId is a patient id.

I need a Query that will delete all the rows where a patient(EyeId) has been treated by anything other than the specific treatment i am interested in.

If i am looking for patients treated with PDT then i want to keep the patients that have ONLY been treated with PDT. The rest must be deleted.

Patients treated with something else, or patients that have been treated with PDT and something else must be deleted from the table.

I guess (using logic from my previous question) i must delete patients where the sum of all their treatments does not equal the sum of their treatments with PDT i.e. the patients that have changed treatment.

And then also delete every row that is not PDT i.e. the patients treated with something else-

Just dont know how to express this as a delete statement.

Thanks
0
Question by:soozh

LVL 19

Expert Comment

ID: 39228974
you mean to say this

delete from #table1
where TreatedWith <> 'PDT'
0

LVL 8

Expert Comment

ID: 39228980
delete from #YearlyTreatmentData
where
treatedwith <> N'PDT';
0

LVL 8

Expert Comment

ID: 39228986
As a completely unrelated to the answer question, why don't you filter the inserts into the temp table based on the treatedwith column?
0

Author Comment

ID: 39229015
no that is not the answer!

Ofcourse i know how to delete patients that dont have PDT!

I need to delete patients that have PDT + something else, and all those that only have something else.

Your solution only deletes the second group - patients who have been treated with something else.

However i first need to delete patients that have changed treatment.
0

LVL 8

Accepted Solution

didnthaveaname earned 167 total points
ID: 39229098
Is there only one treatedWith value per entry (will there be multiple rows with the same eyeID if they were treated with something else ) ?

Edit:

If yes, I think the following would work:

``````delete from YTD
from
#YearlyTreatmentData as YTD
left join #YearlyTreatmentData as YTDO on YTD.eyeID = YTDO.eyeID and YTDO.treatedWith <> YTD.treatedWith
where
YTDO.eyeID is not null or
YTD.treatedWith <> N'PDT';
``````

(if you have any sample data, that would be quite helpful for testing =))

Edit of Edit:

I changed the query to remove an extraneous filter.
0

LVL 31

Assisted Solution

awking00 earned 167 total points
ID: 39229184
delete from yearlytreatmentdata
where eyeid in
(select eyeid from
(select eyeid from yearlytreatmentdata where treatedwith <> 'PDT')
union
(select eyeid from yearlytreatmentdata where treatedwith = 'PDT'
intersect
select eyeid from yearlytreatmentdata where treatedwith <> 'PDT')
);
0

LVL 40

Assisted Solution

Sharath earned 166 total points
ID: 39229743
you can also try this.
``````DELETE FROM #yearlytreatmentdata t1
WHERE  NOT EXISTS (SELECT 1
FROM   #yearlytreatmentdata t2
WHERE  t1.EyeId = t2.EyeId
GROUP  BY t2.EyeId
HAVING COUNT(DISTINCT t2.TreatedWith) = 1
AND MAX(t2.TreatedWith) = 'PDT')
``````
0

## Featured Post

### Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…