SQL Clone
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 Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1648 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 Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 285
This deletes all the columns from the table
JohnG69
JohnG69
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1093 Visits: 453
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1648 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
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71648 Visits: 40932
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1093 Visits: 453
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 Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 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
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71648 Visits: 40932
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
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 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