delete duplicate record problem

create table tbl1(
fld1 varchar(10),
fld2 varchar(10))
insert into tbl1 Values('joe','x')
insert into tbl1 Values('joe','y')
insert into tbl1 Values('joe','z')
insert into tbl1 Values('bill','x')
insert into tbl1 Values('sam','z')
insert into tbl1 Values('ted','x')
insert into tbl1 Values('ted','y')
insert into tbl1 Values('ed','x')
insert into tbl1 Values('mary','x')
insert into tbl1 Values('sue','z')
insert into tbl1 Values('tom','x')
insert into tbl1 Values('tom','y')
insert into tbl1 Values('tom','z')
insert into tbl1 Values('frank','x')
insert into tbl1 Values('samir','x')
insert into tbl1 Values('li','x')
insert into tbl1 Values('li','z')
insert into tbl1 Values('cindy','z')
insert into tbl1 Values('bert','x')
insert into tbl1 Values('jon','x')
--the statement below contains duplicate records.
SELECT fld1, fld2
FROM tbl1
WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVING
Count(*)>1 )
--I want to remove/delete the records from tbl1 where fld1 has count(fld1)
> 1 and fld2 = 'z' -- joe, x, joe, y, joe, z| tom, x, tom, y, tom, z| li, x,[/color
]
li, z.
joe, tom, li all have a count(fld1) > 1, and they all contain a 'z' in fld2.
I do not want to delete rows where count(fld1) = 1 and fld2 = 'z'. Only
remove rows where count(fld1)>1 and fld2 = 'z'.
--Here is what I tried that did not work:
Delete from tbl1
Where fld2 in
(SELECT fld1, fld2
FROM tbl1
WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVING
Count(*)>1 ))
and fld2 = 'z'
Any suggestions appreciated how this could be accomplished.
Thanks,
RichTry THis:
Delete tbl1
Where fld1 In
(SELECT fld1
FROM tbl1
GROUP BY fld1
HAVING Count(*)>1 )
And Fld2 = 'z'
"Rich" wrote:

> create table tbl1(
> fld1 varchar(10),
> fld2 varchar(10))
> insert into tbl1 Values('joe','x')
> insert into tbl1 Values('joe','y')
> insert into tbl1 Values('joe','z')
> insert into tbl1 Values('bill','x')
> insert into tbl1 Values('sam','z')
> insert into tbl1 Values('ted','x')
> insert into tbl1 Values('ted','y')
> insert into tbl1 Values('ed','x')
> insert into tbl1 Values('mary','x')
> insert into tbl1 Values('sue','z')
> insert into tbl1 Values('tom','x')
> insert into tbl1 Values('tom','y')
> insert into tbl1 Values('tom','z')
> insert into tbl1 Values('frank','x')
> insert into tbl1 Values('samir','x')
> insert into tbl1 Values('li','x')
> insert into tbl1 Values('li','z')
> insert into tbl1 Values('cindy','z')
> insert into tbl1 Values('bert','x')
> insert into tbl1 Values('jon','x')
> --the statement below contains duplicate records.
> SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 )
> --I want to remove/delete the records from tbl1 where fld1 has count(fld1
)
> li, z.
> joe, tom, li all have a count(fld1) > 1, and they all contain a 'z' in fld
2.
> I do not want to delete rows where count(fld1) = 1 and fld2 = 'z'. Only
> remove rows where count(fld1)>1 and fld2 = 'z'.
> --Here is what I tried that did not work:
> Delete from tbl1
> Where fld2 in
> (SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 ))
> and fld2 = 'z'
> Any suggestions appreciated how this could be accomplished.
> Thanks,
> Rich|||Thanks very much.
"CBretana" wrote:
> Try THis:
> Delete tbl1
> Where fld1 In
> (SELECT fld1
> FROM tbl1
> GROUP BY fld1
> HAVING Count(*)>1 )
> And Fld2 = 'z'
> "Rich" wrote:
>|||And for the future, use UNIQUE constraints to ensure your data doesn't get
this way in the first place.
-- Alex
"Rich" wrote:

> create table tbl1(
> fld1 varchar(10),
> fld2 varchar(10))
> insert into tbl1 Values('joe','x')
> insert into tbl1 Values('joe','y')
> insert into tbl1 Values('joe','z')
> insert into tbl1 Values('bill','x')
> insert into tbl1 Values('sam','z')
> insert into tbl1 Values('ted','x')
> insert into tbl1 Values('ted','y')
> insert into tbl1 Values('ed','x')
> insert into tbl1 Values('mary','x')
> insert into tbl1 Values('sue','z')
> insert into tbl1 Values('tom','x')
> insert into tbl1 Values('tom','y')
> insert into tbl1 Values('tom','z')
> insert into tbl1 Values('frank','x')
> insert into tbl1 Values('samir','x')
> insert into tbl1 Values('li','x')
> insert into tbl1 Values('li','z')
> insert into tbl1 Values('cindy','z')
> insert into tbl1 Values('bert','x')
> insert into tbl1 Values('jon','x')
> --the statement below contains duplicate records.
> SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 )
> --I want to remove/delete the records from tbl1 where fld1 has count(fld1
)
> li, z.
> joe, tom, li all have a count(fld1) > 1, and they all contain a 'z' in fld
2.
> I do not want to delete rows where count(fld1) = 1 and fld2 = 'z'. Only
> remove rows where count(fld1)>1 and fld2 = 'z'.
> --Here is what I tried that did not work:
> Delete from tbl1
> Where fld2 in
> (SELECT fld1, fld2
> FROM tbl1
> WHERE tbl1.fld1 In (SELECT t1.fld1 FROM [tbl1] As t1 GROUP BY t1.fld1 HAVI
NG
> Count(*)>1 ))
> and fld2 = 'z'
> Any suggestions appreciated how this could be accomplished.
> Thanks,
> Rich