Delete statement [message #39759] |
Wed, 07 August 2002 12:34 |
kmsav
Messages: 3 Registered: January 2002
|
Junior Member |
|
|
I want to delete from a Medicine table where if the count of medicine from an Allergy table is 2 or more. If I use this select statement I can get the lines that have more than2
select distinct alrg.medicine, count(alrg.medicine)
,med.name from allergies alrg, medicines med
where med.id = alrg.medicine
having count(alrg.medicine) >= 2
group by alrg.medicine, med.name
I tried to do this:
delete from medicines where id in(
select distinct alrg.medicine, count(alrg.medicine)
,med.name from allergies alrg, medicines med
where med.id = alrg.medicine
having count(alrg.medicine) >= 2
group by alrg.medicine, med.name)
but I get a "too many values" error.
The medicine table has: Name, ID, Make
The allergy table has: Patient ID, Medicine
|
|
|
|