delete repeating rows

Question

Post reply

delete repeating rows

Viewing 7 posts - 16 through 21 (of 21 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

Another way of deleting duplicates is to use the hidden row identifier %%LockRes%% to identify the individual rows

using the test data post previously

DELETE FROM #Duplicates

WHERE %%LockRes%%

NOT IN (

SELECT MIN(%%LockRes%%) FROM #Duplicates

GROUP BY somevalue)

Paul White SSC Guru Points: 150467 More actions · Answer 2

Hey Steve,

I'm guessing that you are not serious here - but you should have posted a warning not to use that code! NEVER use %% functions for anything but exploration and fun - they are undocumented might therefore do anything!

As it happens, %%LockRes%% appears to identify a locking hash - which isn't guaranteed to be unique, or relate to a single row - bad news here!

(%%PhysLoc%% in 2008 is different, but equally not to be relies on for anything ever)

Also be careful with NOT IN and NULLs - and remember that these %% functions aren't guaranteed to do anything at all - it might return NULL one day, for all we know 😉

Summary: if you use %% functions, your shoes might catch fire or your fridge might float away. 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

steveb. SSC-Forever Points: 46733 More actions · Answer 3

Thanks Paul,

We were playing around with them today and thought they may be useful, i guess it can be dangerous to use undocumented features and i would never use it in production.

I was also hoping to hear what other more experienced people had to say about them as i couldn't find much info on google, so i am glad you posted.

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

When I have this problem, I "cheat" if I can.

I create a temp table, with a unique index on the column containing duplicates.

Then set "ignore duplicates" on the index.

Then copy from your live table containing duplicates into the temp table. It will only allow one record and delete the others.

Then truncate the live data & copy back in from the temp table.

This may not work if you have huge tables, or cannot truncate your live table for a variety of reasons, but it is an option.

(Maybe we should merge the 2 duplicate threads ? :-D)

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 5

Thanks all for your reply......

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

cheyursenthil Newbie Points: 3 More actions · Answer 6

HI , JUST TRY TO THIS

alter table <TABLENAME> add gid timestamp

delete from <TABLENAME> where gid not in(select max(gid) from <TABLENAME> group by Field1,Field2)

alter table <TABLENAME> drop column gid

Note: Field1,Field2 are repeating allfields except that gid

gid is nothid that is uniqu field

i can fix lot of issue in production database in this way........

Paul White SSC Guru Points: 150467 More actions · Answer 7

cheyursenthil (4/15/2010)
HI , JUST TRY TO THIS
alter table <TABLENAME> add gid timestamp
delete from <TABLENAME> where gid not in(select max(gid) from <TABLENAME> group by Field1,Field2)
alter table <TABLENAME> drop column gid
Note: Field1,Field2 are repeating allfields except that gid
gid is nothid that is uniqu field
i can fix lot of issue in production database in this way........

That's a great way to fragment your table, and is a lot less efficient than the direct methods (e.g. using ROW_NUMBER).

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi