Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Delete Duplicates from table with no identity column


Delete Duplicates from table with no identity column

Author
Message
AR-777
AR-777
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 285
Here is a sample of the table with 4 columns

ANTON   NULL   05023   555-3932   
ANTON   NULL   05023   555-3932   
AROUT   NULL   04423   555-7788   
AROUT   NULL   04423   555-7788   
BERGS   NULL   07623   123-5665   
BERGS   NULL   07623   123-5665   
BLAUS   NULL   68306   084-5660


how would i delete duplicates from this table.
I know some delete queries but they only work for tables with identity columns but this has all varchar fields.

Can you help me find how to delete duplicates from this
Slick84
Slick84
Mr or Mrs. 500
Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)

Group: General Forum Members
Points: 500 Visits: 1163
How about:

SELECT dupeColumn,
COUNT(dupeColumn) AS NumOccurrences
FROM users
GROUP BY dupeColumn
HAVING ( COUNT(dupeColumn) > 1 )



--
Hehe
AR-777
AR-777
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 285
This deletes all the columns from the table
JohnG69
JohnG69
Mr or Mrs. 500
Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)

Group: General Forum Members
Points: 569 Visits: 452
you need to use the ROW_NUMBER() function

by using the ROW_Number function every row will have a different row id.

I personally never used it but I saw my ex-colleague use it.
Slick84
Slick84
Mr or Mrs. 500
Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)

Group: General Forum Members
Points: 500 Visits: 1163
ar-727381 (9/25/2009)
This deletes all the columns from the table


I'm not sure I understand. It deletes all columns from the table? Crazy

--
Hehe
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38937
ok here's a 2005 secret: there is a hidden row identifier in every table row named %%LockRes%%,
in 2008 they renamed the identifier to %%physloc%%

you can use that as part of a delete statment:

Create Table myHeap(HeapName varchar(30),SomeNullColumn int,SomeId int,phone varchar(8))
INSERT INTO myHeap
SELECT 'ANTON',NULL,05023,'555-3932' UNION ALL
SELECT 'ANTON',NULL,05023,'555-3932' UNION ALL
SELECT 'AROUT',NULL,04423,'555-7788' UNION ALL
SELECT 'AROUT',NULL,04423,'555-7788' UNION ALL
SELECT 'BERGS',NULL,07623,'123-5665' UNION ALL
SELECT 'BERGS',NULL,07623,'123-5665' UNION ALL
SELECT 'BLAUS',NULL,68306,'084-5660'

select
%%LockRes%%,
myHeap.*
from myHeap
/*
--results:
LockRes HeapName SomeNullColumn SomeId phone
1:35564:0 ANTON NULL 5023 555-3932
1:35564:1 ANTON NULL 5023 555-3932
1:35564:2 AROUT NULL 4423 555-7788
1:35564:3 AROUT NULL 4423 555-7788
1:35564:4 BERGS NULL 7623 123-5665
1:35564:5 BERGS NULL 7623 123-5665
1:35564:6 BLAUS NULL 68306 084-5660
*/

--delete works! note that the identifier was unique to my machine...yours will generate a different value!
delete from myHeap where %%LockRes%% ='1:35564:0'



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!

JohnG69
JohnG69
Mr or Mrs. 500
Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)

Group: General Forum Members
Points: 569 Visits: 452
This is great to know...

I've used the previous post to delete all duplicates....

Thanks

Select min(%%LockRes%%) as ident,* into #test from MyHeap group by HeapName, SomeNullColumn, SomeId, phone

delete from M
from MyHeap M
left join #test T on T.ident = M.%%LockRes%%
where T.HeapName is null

Select * from MyHeap


I really like this hidden feature...
AR-777
AR-777
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 285
i already have this table created. i just gave you the top few rows of it. I dont think i can update the table with %%LockRes%%
so will need some more help to achieve deletion of duplicates.

by the way. it was nice to know about the hidden feature. thanks a lot
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14942 Visits: 38937
ar-727381 (9/25/2009)
i already have this table created. i just gave you the top few rows of it. I dont think i can update the table with %%LockRes%%
so will need some more help to achieve deletion of duplicates.

by the way. it was nice to know about the hidden feature. thanks a lot


ALL tables, including the one you created, have this....it's just hidden:
try SELECT %%LockRes%%,* FROM YOURTABLE

you'll see it exists, and so you could adapt our example solutions to do the same.
jghali 's solution is so clean and simple, I would highly recommend it...just change the group columns to your real columns.

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!

321 MySQL
321 MySQL
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 313
try this and give me feedback:


with duplikate as (
SELECT row1,row2,row3,
row_Number () Over (partition by row1,row2,row3 order by row1) -- sortierung ist eigentlich egal
as RowNumber
FROM Table
)
delete from duplikate
where RowNumber >=2



Hehe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search