Deletion of Duplicate Row

[Richard]

Hi Everyone,

I have a table in which their is record which is exactly same.
I want to delete all the duplicate keeping ony 1 record in a table.
Example

Table A
Empid currentmonth Previousmonth
Supplimentarydays basic

158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.00
4701.00
158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.00
4701.00
158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.00
4701.00
I want to delete 2 rows of above table.How can I achieve that.
Any suggestion how can i do that.
Thank you in advance
Richard

[hwoess]

Am 2 Jul 2005 00:10:03 -0700 schrieb Richard:

Hi Everyone,

I have a table in which their is record which is exactly same.
I want to delete all the duplicate keeping ony 1 record in a table.
Example

Table A
Empid currentmonth Previousmonth
Supplimentarydays basic

158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.00
4701.00
158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.00
4701.00
158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.00
4701.00
I want to delete 2 rows of above table.How can I achieve that.
Any suggestion how can i do that.
Thank you in advance
Richard

there are some possibilities:
a) you can add a new column with type autoident, then you have no duplicate
rows and can delete them by hand or by script and afterwards you can remove
this column (but i think it is very bad table design having no primary key)
b) execute this script in the QueryAnalizer:
select distinct * into #tmp from myTable
delete myTable
insert into myTable select * from #tmp
(save your database before!!)

bye,
Helmut

[Richard]

hi Helmut,

a) you can add a new column with type autoident, then you have no
duplicate

rows and can delete them by hand or by script and afterwards you can remove
this column (but i think it is very bad table design having no primary key)
b) execute this script in the QueryAnalizer:
select distinct * into #tmp from myTable
delete myTable
insert into myTable select * from #tmp
Thank you, your both points works fine.

cheers :)
Richard

hwoess wrote: Am 2 Jul 2005 00:10:03 -0700 schrieb Richard:

Hi Everyone,

I have a table in which their is record which is exactly same.
I want to delete all the duplicate keeping ony 1 record in a table.
Example

Table A
Empid currentmonth Previousmonth
Supplimentarydays basic

158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.00
4701.00
158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.00
4701.00
158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.00
4701.00
I want to delete 2 rows of above table.How can I achieve that.
Any suggestion how can i do that.
Thank you in advance
Richard

there are some possibilities:
a) you can add a new column with type autoident, then you have no duplicate
rows and can delete them by hand or by script and afterwards you can remove
this column (but i think it is very bad table design having no primary key)
b) execute this script in the QueryAnalizer:
select distinct * into #tmp from myTable
delete myTable
insert into myTable select * from #tmp
(save your database before!!)

bye,
Helmut