[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to check duplicate records in a table

Posted on 2007-09-29
5
Medium Priority
?
1,817 Views
Last Modified: 2008-01-09
Hi,
I need help on how to check(select) duplicate records in a table and then delete them?

One Query to display the duplicate record. One query to delete the duplicate records but keep the latest one. Example,

Table A,
F Name,  L Name,  Date
a1           b1           09/21
a2          b2            09/22
a1           b1           09/23

I want to display 1st and 3rd records in Table A to show they are duplicated base on F Name and L Name. Then delete the first record that is the older one.

Thanks a lot.

kate

0
Comment
Question by:kate_y
  • 2
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19985185
what about this:

delete `Table A` t
where where exists ( select null from `Table A` p where p.`F Name` = t.`F Name` and p.`L Name` = t.`L Name` and p.`Date` > t.`Date` )

0
 
LVL 6

Expert Comment

by:SamsonChung
ID: 19985261
you want to do this in just command line sql? Or PL/SQL?

command line sql would be rather time consuming.

I would suggest you use PL/SQL to attack this problem, it becomes so much easier.

Create a temp table,

SELECT all the distinct FNAME and LNAME matches into a cursor

loop the cursor,

SELECT the latest date FROM TableA where FNAME and LNAME exists.
Store date into temp table, with Fname and LName.

Finish Loop

drop TableA,
Rename TempTable to TableA

this should fix your problem.
 
0
 
LVL 6

Expert Comment

by:SamsonChung
ID: 19985269
Just in case you don't know how to get the distinct fname and lname from the table,

what you do is, you dump TableA to a new table drop the Date column

the select Distinct from this table would give you a distinct fname and lname table. from tableA
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19985293
IMPORTANT:

 you posted in the MySQL zone, but put the tags:
 Microsoft,  MS Access,  2002,  Query

  which is not the same!
  please specify !!!


my syntax is for MySQL, for Ms Access:

delete [Table A] t
where where exists ( select null from [Table A] p where p.[F Name] = t.[F Name] and p.[L Name] = t.[L Name] and p.[Date] > t.[Date] )


@SamsonChung:
>I would suggest you use PL/SQL to attack this problem, it becomes so much easier.
I would actually highly disrecommend any solution like that, as they will be much slower, at least in most cases...



0
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 2000 total points
ID: 19985825
First query to show duplicate records:

    SELECT * FROM [Table A] AS main_tbl LEFT OUTER JOIN
    (SELECT [F Name], [L Name], Count(*) AS num_records FROM [Table A]
    GROUP BY [F Name], [L Name]) AS count_tbl
    ON main_tbl.[F Name] = count_tbl.[F Name] AND main_tbl.[L Name] = count_tbl.[L Name]
    WHERE count_tbl.num_records > 1

Second query to delete duplicate records:

    DELETE FROM [Table A] AS main_tbl LEFT OUTER JOIN
    (SELECT [F Name], [L Name], Count(*) AS num_records, Max(Date) AS max_date FROM [Table A]
    GROUP BY [F Name], [L Name]) AS count_tbl
    ON main_tbl.[F Name] = count_tbl.[F Name] AND main_tbl.[L Name] = count_tbl.[L Name]
    WHERE count_tbl.num_records > 1 AND main_tbl.[Date] < count_tbl.max_date

Not sure I have the delete syntax right. You'll prob want to backup your data and run a small test.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question