Delete Duplicates from table with no identity column

Question

Post reply

Delete Duplicates from table with no identity column

AR-777

Ten Centuries

Points: 1288
More actions
September 25, 2009 at 6:29 am

#96329

Here is a sample of the table with 4 columns
ANTONNULL05023555-3932
ANTONNULL05023555-3932
AROUTNULL04423555-7788
AROUTNULL04423555-7788
BERGSNULL07623123-5665
BERGSNULL07623123-5665
BLAUSNULL68306084-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

Viewing 15 posts - 1 through 15 (of 23 total)

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

Slick84 SSCertifiable Points: 5602 More actions · Answer 1

How about:

SELECT dupeColumn,

COUNT(dupeColumn) AS NumOccurrences

FROM users

GROUP BY dupeColumn

HAVING ( COUNT(dupeColumn) > 1 )

--
:hehe:

AR-777 Ten Centuries Points: 1288 More actions · Answer 2

AR-777

Ten Centuries

Points: 1288

September 25, 2009 at 7:10 am

#1058434

This deletes all the columns from the table

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

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 SSCertifiable Points: 5602 More actions · Answer 4

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 SSC Guru Points: 323518 More actions · Answer 5

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 'AROUT',NULL,04423,'555-7788' 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 Hall of Fame Points: 3114 More actions · Answer 6

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 Ten Centuries Points: 1288 More actions · Answer 7

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 SSC Guru Points: 323518 More actions · Answer 8

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 Right there with Babe Points: 760 More actions · Answer 9

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:

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

WOW...

That one works too.

I just need to try to understand it.:-)

AR-777 Ten Centuries Points: 1288 More actions · Answer 11

Just what i was looking for...

works perfectly

thank you so much

Slick84 SSCertifiable Points: 5602 More actions · Answer 12

Lowell (9/25/2009)
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'

Nice.. this is interesting stuff... is it always unique though? And what's allowed using this? Updates, Inserts, Deletes?

I'm not so sure if I could permanently use this as an identity column, but it definitely would be extremely helpful in certain situations.....

Awesome information. Thanks.

--
:hehe:

Lowell SSC Guru Points: 323518 More actions · Answer 13

it's the actual physical address: i think the format is [file_id]:[page_id]:[slot_id], so it's the true physical address on your server;

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 Right there with Babe Points: 760 More actions · Answer 14

jghali (9/25/2009)
WOW...
That one works too.
I just need to try to understand it.:-)

if you have further question or need help ,don´t hesitate to ask here 😎