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 Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 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
Right there with Babe
Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)

Group: General Forum Members
Points: 756 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 Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 285
This deletes all the columns from the table
JohnG69
JohnG69
Say Hey Kid
Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)

Group: General Forum Members
Points: 709 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
Right there with Babe
Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)Right there with Babe (756 reputation)

Group: General Forum Members
Points: 756 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
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28064 Visits: 39926
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
Say Hey Kid
Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)

Group: General Forum Members
Points: 709 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 Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 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
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28064 Visits: 39926
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 (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)

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