How to delete rows in a table when no primary key is defined

Hello,

I want to delete duplicate rows in a table when no primary key is
defined.
For eg: If we have table1 with data as below,

Suma 23 100
Suma 23 100

I want to delete a row from this table and retain only one row.

I tried deleting self joins and exists operator. But it is deleting
both the rows. I want to retain one row.

Can anybody help me out.

Thanks in advance,
Suma

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Dis...pict221110.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=760520Add a identity column to the table and delete the row with de min value.|||Patarroxa wrote:
> Add a identity column to the table and delete the row with de min
> value.

Or copy the data with a SELECT DISTINCT into another table, drop the
original and rename the new table.

robert|||Create a new table (with a key), then use SELECT DISTINCT or GROUP BY to
populate it from the old one.

--
David Portas
SQL Server MVP
--|||"suma" wrote:
> Hello,
> I want to delete duplicate rows in a table when no primary key
> is defined.
> For eg: If we have table1 with data as below,
> Suma 23 100
> Suma 23 100
> I want to delete a row from this table and retain only one
> row.
> I tried deleting self joins and exists operator. But it is
> deleting both the rows. I want to retain one row.
> Can anybody help me out.
> Thanks in advance,
> Suma

Thanks for the response.
But it has to be done using a single sql statement.
Using multiple we can do it...is there any way to do using a single
sql statement.
Thanks,
Suma|||First of all this is not a table by definition. A table must have a
key. And the answer is No, it will take more than one statement to
clean up the base table -- either a cursor, an IDENTITY or a SELECT
DISTINCT. You can put the SELECT DISTINCT into a VIEW as a kludge.

You did fire the guy that did this, didn't you?|||Using a single DELETE statement it can't be done if there is no way to
differentiate between the rows. That's why a primary key is supposed to
be mandatory. Why should you have a table without a key?

--
David Portas
SQL Server MVP
--|||You're not really saving anything doing it this way over the alternatives,
and it's *very* slow for large numbers of duplicates.

SET ROWCOUNT=1

DELETE table1
WHERE EXISTS (SELECT *
FROM table1 AS t2
WHERE table1.col1 = t2.col1 and table1.col2 = t2.col2 and table1.col3 =
t2.col3
GROUP BY t2.col1, t2.col2, t2.col3
HAVING COUNT(*) > 1)

WHILE @.@.ROWCOUNT>0
DELETE ... --same statement all over

Disclaimer: This is not tested. I am not responsible for any loss of data
incurred by use of this technique. SELECT INTO with GROUP BY is probably
safest and fastest, as recommended by others.

Also see books online, Index, DELETE (described), and the description of
DELETE FROM table WHERE CURRENT OF cursor_name

"suma" <DoNotEmail@.dbForumz.com> wrote in message
news:4_761872_6b47fee83970ff4272fca067cae7180d@.dbf orumz.com...
> "suma" wrote:
> > Hello,
> > I want to delete duplicate rows in a table when no primary key
> > is defined.
> > For eg: If we have table1 with data as below,
> > Suma 23 100
> > Suma 23 100
> > I want to delete a row from this table and retain only one
> > row.
> > I tried deleting self joins and exists operator. But it is
> > deleting both the rows. I want to retain one row.
> > Can anybody help me out.
> > Thanks in advance,
> > Suma
> Thanks for the response.
> But it has to be done using a single sql statement.
> Using multiple we can do it...is there any way to do using a single
> sql statement.
> Thanks,
> Suma|||And thats why oracle we can delete the duplicate rows using rowid or rownum
and not in sql server. Some unique identity has to be there !!|||True, but with correct design you'll never need to. The problem IS
soluble in SQL Server too, it's just that SQL Server requires that you
fix things rather than allow you to live with such a kludgy solution.

--
David Portas
SQL Server MVP
--|||>> Oracle we can delete the duplicate rows using rowid or rownum and
not in sql server <<

Yes, Oracle is a sequential file system and a piss-poor RDBMS under the
covers. Parallelism, set processing, and all the other things that
allow a good SQL implmentation to run 4 to 5 orders of magnitude faster
and 80-90% smaller are not available in Oracle and cannot be because of
a horrible architecture. Look up the performance for Nucleus (Sand
Technology) and other VLDB products.|||There is a way to do this, but it will take massive amounts of time to
delete many rows, because the duplicate rows are deleted one row at a
time. It goes like this:

SET ROWCOUNT 1
-- Generate a rowcount > 0
SELECT COUNT(*) FROM MyTable
While @.@.rowcount > 0
Begin
DELETE MyTable
WHERE (
SELECT COUNT(*)
FROM MyTable T1
WHERE T1.Col1 = MyTable.Col1
AND T2.Col2 = MyTable.Col2
) > 1
End
-- don't forget this line!
SET ROWCOUNT 0

Hope this helps,
Gert-Jan

suma wrote:
> Hello,
> I want to delete duplicate rows in a table when no primary key is
> defined.
> For eg: If we have table1 with data as below,
> Suma 23 100
> Suma 23 100
> I want to delete a row from this table and retain only one row.
> I tried deleting self joins and exists operator. But it is deleting
> both the rows. I want to retain one row.
> Can anybody help me out.
> Thanks in advance,
> Suma
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL: http://www.dbforumz.com/General-Dis...pict221110.html
> Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=760520

How to delete duplicate rows

Hi,
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
Herbert
Hi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:

> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default...44&Product=sql
AMB
"Herbert" wrote:

> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:

> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> than
> having
>
>

How to delete duplicate rows

Hi,
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
HerbertHi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:

> Hi,
> Currently we are having one large table in that we are having more tha
n
> 1 lacs record but most of the records are duplicates(All columns are havin
g
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"Herbert" wrote:

> Hi,
> Currently we are having one large table in that we are having more tha
n
> 1 lacs record but most of the records are duplicates(All columns are havin
g
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:

> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> than
> having
>
>

How to delete duplicate rows

Hi,
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
HerbertHi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444&Product=sql
AMB
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:
> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> > Hi,
> > Currently we are having one large table in that we are having more
> than
> > 1 lacs record but most of the records are duplicates(All columns are
> having
> > same values).
> > How can i delete the particular duplicate row?
> >
> > Please give me a solution as soon as possible
> >
> > Thanks,
> > Herbert
>
>

How to delete Duplicate records from table

i have table and in that i have number of records which are duplicated.
i want to delete the duplicate records from this table.
pls help me.CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"shiva" <bany.shanker@.gmail.com> wrote in message
news:1133445445.278560.128440@.g14g2000cwa.googlegroups.com...
>i have table and in that i have number of records which are duplicated.
> i want to delete the duplicate records from this table.
> pls help me.
>|||Unfrotunately, I have not found a clean way to do this. Uri's method will
work great, but technically, his table doesn't have duplicate records (as th
e
identity column prevents that).
It you have true duplicates, here's what I do:
1) Create a table containing all the table's fields plus a count field.
CREATE TABLE #xxx
(
Field1 int,
Field2 int,
NumToDelete int
)
2) Select the records which are duplicate:
INSERT INTO #xxx
SELECT *, COUNT(*) - 1
FROM dupedtable
GROUP BY Field1, Field2
HAVING COUNT(*) > 1
3) Either loop or generate a cursor and go through each record in #xxx
perfoming the following (note, if you loop, you'll need an identity column i
n
#xxx)
SET ROWCOUNT @.NumToDelete
DELETE FROM dupedtable
WHERE Field1 = @.Field1 AND Field2 = @.Field2
That's about it.
Marc
"shiva" wrote:

> i have table and in that i have number of records which are duplicated.
> i want to delete the duplicate records from this table.
> pls help me.
>|||Marc L. Allen wrote:
> Unfrotunately, I have not found a clean way to do this. Uri's method will
> work great, but technically, his table doesn't have duplicate records (as
the
> identity column prevents that).
> It you have true duplicates, here's what I do:
> 1) Create a table containing all the table's fields plus a count field.
> CREATE TABLE #xxx
> (
> Field1 int,
> Field2 int,
> NumToDelete int
> )
> 2) Select the records which are duplicate:
> INSERT INTO #xxx
> SELECT *, COUNT(*) - 1
> FROM dupedtable
> GROUP BY Field1, Field2
> HAVING COUNT(*) > 1
> 3) Either loop or generate a cursor and go through each record in #xxx
> perfoming the following (note, if you loop, you'll need an identity column
in
> #xxx)
> SET ROWCOUNT @.NumToDelete
> DELETE FROM dupedtable
> WHERE Field1 = @.Field1 AND Field2 = @.Field2
> That's about it.
> Marc
>
> "shiva" wrote:
>
delete #demo from #demo inner join #Demo d on #demo.idno < d.idno and
#demo.cola = d.cola and #demo.colb = d.colb
Regards|||Here is the process to delete the duplicate records posted some days back by
some body.
1. SELECT * INTO #Temp1 FROM [Table1]
2. TRUNCATE TABLE [Table1]
3. CREATE UNIQUE INDEX [Index1] ON [Table1] (Unique Column Names) WITH
IGNORE_DUP_KEY
4. INSERT INTO [Table1] (Column Names) SELECT (Column Names) FROM [Table1]
5. DROP INDEX [Table1].[Index1]
Note: In 4 th step,it will take the first row in list of duplicates ,rest of
them will be ignored
Thanks
Kumar
"shiva" wrote:

> i have table and in that i have number of records which are duplicated.
> i want to delete the duplicate records from this table.
> pls help me.
>

How to delete duplicate records from a table ?

I uploaded some data about 2 or 3 times and it keep appending it to the
table.

Now I want to keep only first duplicate and delete rest of.

Suppose part number 123 has been added 3 times so I want to keep only 1
record.

ThanksRefer to following url

http://support.microsoft.com/defaul...B;en-us;q139444

--
- Vishal

How to delete duplicate record

I have table by mistake i have lot of duplicate records. How do i delete it
.?
Thanks
Jayhttp://www.aspfaq.com/2431
Then
http://www.aspfaq.com/2509
"Jay Villa" <jayvilla@.community.nospam> wrote in message
news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>I have table by mistake i have lot of duplicate records. How do i delete it
>.?
> Thanks
> Jay
>|||Jay,
Could you post the structure of your table. It would be helpful to know the
column names and primary key columns involved.
Thanks,
Frank Castora
"Jay Villa" <jayvilla@.community.nospam> wrote in message
news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>I have table by mistake i have lot of duplicate records. How do i delete it
>.?
> Thanks
> Jay
>|||Frank
Table looks like this
cbbdacc_account_id --> PK
cbbdacc_desc
cbbdacc_resp_pidm
cbbdacc_balance
-Jay
"Frank Castora" <fccsql@.hotmail.com> wrote in message
news:%23BFoubonFHA.860@.TK2MSFTNGP12.phx.gbl...
> Jay,
> Could you post the structure of your table. It would be helpful to know
> the column names and primary key columns involved.
> Thanks,
> Frank Castora
> "Jay Villa" <jayvilla@.community.nospam> wrote in message
> news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>|||> cbbdacc_account_id --> PK
Is this an IDENTITY column? Do you need to maintain existing values? If
so, how do you decide which ID # you need to keep?

> cbbdacc_desc
> cbbdacc_resp_pidm
> cbbdacc_balance
Is a row considered a "duplicate" when all three of these columns are
identical in the two rows, or some subset?|||I respectfully defer to Aaron, as the articles he pointed you too are quite
sufficient. :)
Thanks,
Frank Castora
"Jay Villa" <jayvilla@.community.nospam> wrote in message
news:%23AN4KionFHA.4056@.TK2MSFTNGP10.phx.gbl...
> Frank
> Table looks like this
> cbbdacc_account_id --> PK
> cbbdacc_desc
> cbbdacc_resp_pidm
> cbbdacc_balance
>
> -Jay
>
> "Frank Castora" <fccsql@.hotmail.com> wrote in message
> news:%23BFoubonFHA.860@.TK2MSFTNGP12.phx.gbl...
>|||Hi Aaron,
You may want to add:
WITH JustDups AS
(
SELECT * FROM T1 AS A
WHERE surkey <
(SELECT MAX(surkey) FROM T1 AS B
WHERE B.wannabekey = A.wannabekey)
)
DELETE FROM JustDups;
:)
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23zpiXaonFHA.1968@.TK2MSFTNGP14.phx.gbl...
> http://www.aspfaq.com/2431
> Then
> http://www.aspfaq.com/2509
>
>
> "Jay Villa" <jayvilla@.community.nospam> wrote in message
> news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>