Select duplicates on a table and update a certain column if it is duplicate.

Question

Post reply

Select duplicates on a table and update a certain column if it is duplicate.

rfjavellana33

SSC Rookie

Points: 29
More actions
June 3, 2011 at 4:09 pm

#238702

Hi guys,

Good day, I just want to ask some help regarding the query on how to check for duplicates on a table. Here is the example:

Supposing I have TableOld which is on the SQL server then I have this FileA which has the same structure on the TableOld. I will import FileA to the TableOld and some of the records on FileA already exist on TableOld, my question is how to create a query to check if the record from FileA that was imported has a duplicate or already exist on TableOld, if it exist the query also update a certain field from the TableOld to note that it is already existing.

TableOld structure:

Name Phone Lock

Larry 1234567890

FileA structure:

Name Phone

Larry 1234567890

After the import and executing the query:

TableOld Data:

Name Phone Lock

Larry 1234567890 2

Larry 1234567890 2

Note: Data must be imported even it has duplicates, to determine that it has duplicates is the field Lock.

Thank You.

Raff:-)

Viewing 6 posts - 1 through 5 (of 5 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

It's a weird process to import duplicates on purpose and try to figure out there are duplicates afterwards... Why not add those rows to a separate table holding just the duplicate values?

But if you insist in your current process you could use "ROW_NUMBER() OVER(PARTITION BY <your column> ORDER BY <another column>) AS row" in a CTE or subquery and query for row > 1.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 2

I'm in agreement with Lutz.

Morevover - just out of curiosity, what are you planning to do with duplicate rows after the update process is done?

delete them?

keep them there forever?

Last but not least, is this a one off process or is this a recurrent one?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

@rfjavellana33,

Are you all set on this or do you still need help?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions