471,049 Members | 1,497 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,049 software developers and data experts.

Deletion of Duplicate Row

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

Jul 23 '05 #1
2 1439
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

Jul 23 '05 #2
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


Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Tuhin Kumar | last post: by
6 posts views Thread by sumit | last post: by
2 posts views Thread by Christopher Pisz | last post: by
1 post views Thread by Dan | last post: by
2 posts views Thread by Rune Froysa | last post: by
3 posts views Thread by A_Republican | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.