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
Supplimentaryda ys 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]

Re: Deletion of Duplicate Row

Am 2 Jul 2005 00:10:03 -0700 schrieb Richard:
[color=blue]
> 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
> Supplimentaryda ys 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[/color]

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]

Re: Deletion of Duplicate Row

hi Helmut,

a) you can add a new column with type autoident, then you have no
duplicate[color=blue]
> 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[/color]

Thank you, your both points works fine.

cheers :)
Richard

hwoess wrote:[color=blue]
> Am 2 Jul 2005 00:10:03 -0700 schrieb Richard:
>[color=green]
> > 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
> > Supplimentaryda ys 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[/color]
>
> 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[/color]