soozh
asked on
Query to delete rows
I have a tabled defined as:
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
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
delete from #YearlyTreatmentData
where
treatedwith <> N'PDT';
where
treatedwith <> N'PDT';
As a completely unrelated to the answer question, why don't you filter the inserts into the temp table based on the treatedwith column?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
delete from #table1
where TreatedWith <> 'PDT'