|
Home > Archive > Microsoft SQL Server forum > July 2005 > Deletion of Duplicate Row
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Deletion of Duplicate Row
|
|
| Richard 2005-07-02, 3:25 am |
| 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 2005-07-02, 3:25 am |
| 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 2005-07-02, 7:23 am |
| 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:
>
>
> 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
|
|
|
|
|