Cloud and Microsoft technologies enthusiast architect in Switzerland RSS 2.0
# Friday, July 29, 2005

Au détour d'une rencontre avec un guru du SQL, j'ai pu admirer le code suivant, permettant d'effacer tous le doublon d'une table. Imaginons une table (aTable) avec une seule colonne (aNumber), aucune clé, mais avec plusieurs lignes contenant la même valeur :

1
1
2
1
3
3
3
1
2
1

Le but est du supprimer tous les doublons, ceci en une seule requête. Vous avez la réponse ?

Voici le code SQL :

set rowcount 1

select 1
while @@rowcount > 0
delete test
where 1 < (select count(*) from test t2 where test.id = t2.id)

set rowcount 0

 

 

Some couple of weeks ago, I had the opportunity to meet SQL guru, and I have seen a very nice SQL code, deleting duplicates rows in a table. Let's imagine a table (aTable) with only one field (aNumber), without any key but multiple rows with the same value :

1
1
2
1
3
3
3
1
2
1

The goal is to delete duplicate values in only one SQL request. You have the answed ?

Here is the code :

set rowcount 1

select 1
while @@rowcount > 0
delete test
where 1 < (select count(*) from test t2 where test.id = t2.id)

set rowcount 0

Friday, July 29, 2005 4:06:39 PM (GMT Daylight Time, UTC+01:00)  #    Comments [0] -
Programming | Technical
Google Cloud Platform Certified Professional Cloud Architect
Ranked #1 as
French-speaking SharePoint
Community Influencer 2013
Navigation
Currently Reading :
I was there :
I was there :
I was exhibiting at :
I was there :
I was a speaker at :
I was a speaker at :
I was a speaker at
(January 2013 session):
I was a speaker at :
I was a speaker at :
United Nations (UN) SharePoint Event 2011
I was a speaker at :
I was there !
I was there !
I was there !
I was there !
Archive
<June 2022>
SunMonTueWedThuFriSat
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2022
Yves Peneveyre
Sign In
Statistics
Total Posts: 290
This Year: 0
This Month: 0
This Week: 0
Comments: 20
Themes
Pick a theme:
All Content © 2022, Yves Peneveyre
DasBlog theme 'Business' created by Christoph De Baene (delarou)