Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Delete Duplicates from table with no identity column Expand / Collapse
Author
Message
Posted Friday, September 25, 2009 6:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 22, 2012 8:00 AM
Points: 44, Visits: 282
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
Post #793765
Posted Friday, September 25, 2009 6:46 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163
How about:

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



--
Post #793779
Posted Friday, September 25, 2009 7:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 22, 2012 8:00 AM
Points: 44, Visits: 282
This deletes all the columns from the table
Post #793791
Posted Friday, September 25, 2009 8:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:16 AM
Points: 434, Visits: 202

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.
Post #793864
Posted Friday, September 25, 2009 9:20 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163
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?


--
Post #793893
Posted Friday, September 25, 2009 9:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 12,918, Visits: 32,085
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #793899
Posted Friday, September 25, 2009 9:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:16 AM
Points: 434, Visits: 202
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...
Post #793904
Posted Friday, September 25, 2009 10:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 22, 2012 8:00 AM
Points: 44, Visits: 282
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
Post #793923
Posted Friday, September 25, 2009 10:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 12,918, Visits: 32,085
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #793930
Posted Friday, September 25, 2009 10:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 23, 2010 8:43 AM
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

Post #793937
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse