Delete Duplicates from table with no identity column

Question

Post reply

Delete Duplicates from table with no identity column

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

JohnG69 Hall of Fame Points: 3114 More actions · Answer 1

Thanks for the offer... I read about the "With" Statement... I like it ... Very nice and powerful

Thank You

Slick84 SSCertifiable Points: 5602 More actions · Answer 2

Yes, and for information purposes, those "With" clauses are also called CTE's or 'Common Table Expressions'. It is nice though I have not used it yet. Waiting for a situation where I will need it.

--
:hehe:

Paul White SSC Guru Points: 150467 More actions · Answer 3

Lowell (9/25/2009)
Stuff

It's important to mention that all %% pseudo-columns are undocumented, unsupported, and very cool.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

nandy.chawala Valued Member Points: 51 More actions · Answer 4

nandy.chawala

Valued Member

Points: 51

February 3, 2010 at 5:03 am

#1112760

Good Work Lowell

100 out of 100

Leena

Lowell SSC Guru Points: 323504 More actions · Answer 5

glad this was helpful for you , Leena; I've never found a practical use for it so far, but it is good to know; of course there is always more than one way to do this, and I usually use row_number() , but it's fun to expand the toolbox of scripts a little bit.

nandy.chawala (2/3/2010)
Good Work Lowell
100 out of 100
Leena

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Paul White SSC Guru Points: 150467 More actions · Answer 6

Lowell (2/3/2010)
I've never found a practical use for it so far...

Probably just as well since it is undocumented 😉

I think I used it once to demonstrate lock hash collisions (two rows that generated the same lock hash and hence could deadlock, even though the data rows themselves were unrelated).

I might be misremembering it though 😛

2008 has %%PhysLoc%% and an undocumented decoding function to go with it. See http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-New-(undocumented)-physical-row-locator-function.aspx

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

d.majoor Valued Member Points: 52 More actions · Answer 7

Less sophisticated but what also would work (ans also on sql 2000):

select col1, col2, col3, col4 from Mytable into #tmp_Mytable

group by col1, col2, col3, col4

truncate table Mytable

insert into Mytable select * from #tmp_table

Large tables would require a lot of column typing though.

Paul White SSC Guru Points: 150467 More actions · Answer 8

d.majoor (2/5/2010)
Large tables would require a lot of column typing though.

You could use SELECT DISTINCT * to overcome that (instead of using the group by).

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

aortoll_con Grasshopper Points: 24 More actions · Answer 9

NVM. I just saw someone suggested the same solution I did 🙂