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

Delete Duplicate Record

I have Changed my database schema and transform Data with new keys using
this Query :
SELECT Case_No, Case_Date, Ma7akem_ID, COUNT(*) AS Expr1
FROM Master_Ahkam
GROUP BY Case_No, Case_Date, Ma7akem_ID
HAVING (COUNT(*) > 1)
Due to a Design error in the old one there was a "ModifyDate" in the
primarykey. so the whole record are the same except the Modifydate.. I need
to delete these rows..
Is there anyway to do so'Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. If you had a proper relational
design in the first place, you could not have dups in the first place.
Can you kill the guy that did this? That will improve the overall
quality of your software.|||Hiiiiiiii
The one who designed this is my boss so if I Kill him I 'll Have to Find new
Job, Can you help -;)
Here is the DDL
----
--
CREATE TABLE [AH_Tasneef] (
[ID] [PKInt] NOT NULL ,
[Parent_ID] [PKInt] NOT NULL CONSTRAINT [DF__Tasneef__Parent___0519C6AF]
DEFAULT (0),
[Ma7kama_ID] [PKInt] NOT NULL ,
[Text] [varchar] (200) COLLATE Arabic_BIN NOT NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [PK_AH_Tasneef] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_AH_Tasneef_AH_Ma7akem] FOREIGN KEY
(
[Ma7kama_ID]
) REFERENCES [AH_Ma7akem] (
[ID]
) ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [AH_TasnFakaraat] (
[Tasneef_ID] [PKInt] NOT NULL ,
[Master_ID] [PKInt] NOT NULL ,
[Fakra_No] [int] NOT NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [PK_AH_TasnFakaraat] PRIMARY KEY NONCLUSTERED
(
[Tasneef_ID],
[Master_ID],
[Fakra_No]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [AH_SubMaster] (
[Master_ID] [int] NOT NULL ,
[Fakra_No] [smallint] NOT NULL ,
[Fakra_Text] [text] COLLATE Arabic_BIN NOT NULL ,
[Tasneef_ID] [PKInt] NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [MyKey_PK_1] PRIMARY KEY NONCLUSTERED
(
[Master_ID],
[Fakra_No]
) WITH FILLFACTOR = 80 ON [PRIMARY] ,
CONSTRAINT [FK_AH_SubMaster_AH_Master] FOREIGN KEY
(
[Master_ID]
) REFERENCES [AH_Master] (
[ID]
) ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
alter table dbo.AH_TasnFakaraat
add constraint FK_AH_TASNF_REFERENCE_AH_SUBMA foreign key (Master_ID,
Fakra_No)
references dbo.AH_SubMaster (Master_ID, Fakra_No)
go
alter table dbo.AH_TasnFakaraat
add constraint FK_AH_TASNF_REFERENCE_AH_TASNE foreign key (Tasneef_ID)
references dbo.AH_Tasneef (ID)
go
----
--
I have duplicate records in table AH_TasnFakaraat which i need to clean...
how could i do so'
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133664266.448284.115290@.g47g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. If you had a proper relational
> design in the first place, you could not have dups in the first place.
> Can you kill the guy that did this? That will improve the overall
> quality of your software.
>|||On Sun, 4 Dec 2005 11:38:16 +0200, Islamegy wrote:

>Hiiiiiiii
>The one who designed this is my boss so if I Kill him I 'll Have to Find ne
w
>Job, Can you help -;)
Hi Islamegy,
I can't help you killing your boss or finding you a new job, but I might
be able to help with the duplicates.
I'm not sure exactly which columns are duplicated and which are not, nor
how to decide which rows to retain and which to remove, so I'll post a
generic example for you to modify.
CREATE TABLE Test
(Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
CONSTRAINT TestKey PRIMARY KEY (Col1, Col2, Col3)
)
INSERT INTO Test (Col1, Col2, Col3)
SELECT 1, 2, 3
UNION ALL
SELECT 1, 2, 4
go
The primary key should have been (Col1, Col2). This means that the data
above is incorrect. The lowest value for Col3 should be retained, all
others removed. In the end, only the (1, 2, 3) row should survive.
-- Remove unwanted data
DELETE FROM Test
WHERE EXISTS
(SELECT *
FROM Test AS t2
WHERE t2.Col1 = Test.Col1
AND t2.Col2 = Test.Col2
AND t2.Col3 < Test.Col3)
-- Change key
ALTER TABLE Test
DROP CONSTRAINT TestKey
ALTER TABLE Test
ADD CONSTRAINT TestKey PRIMARY KEY (Col1, Col2)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Delete Duplicate Rec

HI EveryOne

I am facing one prblem that is :
How can be delete duplicate records from table(Database) using query and Sub Query. If any one knows then PLz Forward me at
amit_mant@.rediffmail.com

Quote:

Originally Posted by amitmant

HI EveryOne

I am facing one prblem that is :
How can be delete duplicate records from table(Database) using query and Sub Query. If any one knows then PLz Forward me at
amit_mant@.rediffmail.com

this is the Qry to delete duplicate records from table(Database)

===================QRY======================
delete from table1 where Field1=(select field1 from table1 group by field1 having count(field1)>1)|||This way you will delete all records that have duplicates but if you wish to save single instance it is dangerous proposition.|||

Quote:

Originally Posted by iburyak

This way you will delete all records that have duplicates but if you wish to save single instance it is dangerous proposition.

Can you explain the concept of single instance it is dangerous proposition.sql

delete duplicate orders

I have an order with multiple line items. When the order comes in and put into a table I would like to check that they haven't submitted it twice. Right now I have a stored procedure that can find duplicate lines, but I really need if the whole order ha
s been duplicated then delete it.
So in my store procedure I use:
SELECT COUNT(*) AS Amount,ItemNumber,Store,DeliveryDate,submission,Qu antity
FROM tblItemOrder
GROUP BY ItemNumber,Store,DeliveryDate,submission,Quantity
HAVING COUNT(*) > 1
So this finds duplicate rows. But I need to find if the all rows are duplicated go ahead and delete.
The table structure is fields Amount,ItemNumber,Store,DeliveryDate,submission,Qu antity with no primary key (working table to get the data into shape).
Even if I could find if there are the same amount of rows that have duplicates COUNT(*) > 1, then go ahead and delete the duplicates that would work fine.
hi ashley,
See following example:
--sample data.
create table #cartype(manufacturer varchar(500), itemnumber int)
insert into #cartype values('Toyota',1)
insert into #cartype values('Toyota',1)
insert into #cartype values('Toyota',1)
insert into #cartype values('Honda',2)
insert into #cartype values('Honda',2)
insert into #cartype values('Honda',3)
insert into #cartype values('GE',3)
--deleting all duplicate rows from the table, query will be.
delete a
from #cartype a join
(select manufacturer, itemnumber
from #cartype
group by manufacturer, itemnumber
having count(*) > 1) b on a.manufacturer = b.manufacturer and
a.itemnumber = b.itemnumber
--if you want to keep one row out of the duplicate rows, you will have to
add an identity column to the table.
Ex:
alter table #cartype add idd int identity
--deleting duplicate rows from the table except one
delete from #cartype
where not exists
(Select * from #cartype a
where a.manufacturer = #cartype.manufacturer
and a.itemnumber = #cartype.itemnumber
having min(idd) = #cartype.idd)
--drop the temporary added column
alter table #cartype drop column idd
Vishal Parkar
vgparkar@.yahoo.co.in

Delete duplicate entry

Hi guys,
How can we delete duplicate entries in a table. please send me a
query which delete duplicate entry from table.Manish Sukhija wrote:

> How can we delete duplicate entries in a table. please
> send me a query which delete duplicate entry from table.
This has been discussed a couple of times the last ws:
http://shurl.org/BrNkD
HTH,
Stijn Verrept.

Delete duplicate entries from tables in my database using Query Analyzer

Hello,

How can I delete duplicate entries from tables in my database using Query Analyzer, as there are many duplicate entries in my tables, I want to delete them.

Thanks in advance,
Uday.Does this table contains any unique key or any other key field?|||Hi,

There is seperate id for each entries but duplicate entries have the same id number.

Thanks in advance,
Uday.|||One solution could be adding identity column to this and then deleting the non relevent data.|||You can move all the duplicate ones into a separate temp table using GROUP BY HAVING COUNT(*)>1

Then you delete them using the same clause can use a SELECT DISTINCT to copy them back from the temp table.

Of course if your table is small you can just copy the lot and do a SELECT DISTINCT back!

delete duplicate email addresses

I have an email list that I need to delete duplicates from. How is this
done easily?
This query will tell me that I have duplicates:
Select count(email) from tblTempAutoEmailerGrid1 Where email IN(Select email
from tblSelfTest Group By email Having Count(email) > 1)
This tells me how many clean addresses I have:
Select count(email) from tblTempAutoEmailerGrid1 Where email IN(Select email
from tblSelfTest Group By email Having Count(email) < 2)
How can I delete duplicates? Sometimese there are 3 or 4 of the same
email address in the file.William,
Does the Table have a unique number to identify the Email Addresses?
If so, you could use something like this..
delete from Table
where EmailID in (select max(EmailID)
from Table
group by Email
having (Count(Email)) > 1)
Thanks
Barry|||> I have an email list that I need to delete duplicates from. How is this
> done easily?
The easiest way is to SELECT DISTINCT into another table, and add a primary
key or unique index/constraint to that column so that this doesn't happen.
http://www.aspfaq.com/2431
http://www.aspfaq.com/2509|||First, make sure that the entire record is a duplicate and not just the
email. Otherwise, deleting records containing duplicate emails may result in
losing information. For example, if you have 2 records for John Doe with
same email but different phone numbers, then which record should you delete?
If it is just a list of emails, then you can select distinct from one table
into another table. For example:
select distinct email into TableB from TableA
If you do not want to deal with inserting into a 2nd table but rather
perform regular maintenance on the existing table, then you can delete out
duplicates using the method below. This requires that the table have a
unique key column. Test in the NorthWind sample database:
begin transaction
select count(*) from [Order Details]
delete
from
[Order Details]
where
OrderID <> (select max(X.OrderID) from [Order Details] as X where
X.ProductID = [Order Details].ProductID)
select count(*) from [Order Details]
rollback transaction
"William" <da@.northernit.net> wrote in message
news:NmHTd.40252$H05.29776@.twister.nyroc.rr.com...
> I have an email list that I need to delete duplicates from. How is this
> done easily?
> This query will tell me that I have duplicates:
> Select count(email) from tblTempAutoEmailerGrid1 Where email IN(Select
email
> from tblSelfTest Group By email Having Count(email) > 1)
> This tells me how many clean addresses I have:
> Select count(email) from tblTempAutoEmailerGrid1 Where email IN(Select
email
> from tblSelfTest Group By email Having Count(email) < 2)
> How can I delete duplicates? Sometimese there are 3 or 4 of the same
> email address in the file.
>
>|||Thanks. I did have an autonumber key field. The first method works well if
I execute the query several times to get rid of folks who have their emails
2-4 times in that database.
The addresses are collected when users take a diagnostics test on our
website. Duplicate come about because users find our content really useful.
They take the test or assessment quickly the first time and then 50% of the
time take the assessment again answering the questions with more thought.
We ask the persons age and usually the women lie about their age on the
first pass. When they find out that their results are compared to women
their age they take the test again usually answering the age question 2-5
years older (go figure...). We keep all of the data on every customer over
time. Lots of duplicats...
Anyway, thanks for the help. Worked great.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23a9Or90GFHA.2804@.TK2MSFTNGP10.phx.gbl...
> The easiest way is to SELECT DISTINCT into another table, and add a
primary
> key or unique index/constraint to that column so that this doesn't happen.
> http://www.aspfaq.com/2431
> http://www.aspfaq.com/2509
>