CODEWITHSUNDEEP
mysqlsqldatabase
Burpy Burp
Burpy Burp

Reputation: 457

Duplicate records in a database

I was wondering if somebody has dealt with a similar problem before. I am trying to extract data from a legacy database that I moved to mysql. For some reason its author thought that it was a good idea to duplicate records. So the quote table I deal with looks like:

 id     code     quote_no             client
 1      A      Qte 2013/001-A       John Smith
 2      A/B    Qte 2013/002-A/B     Mark Brown
 3      A      Qte 2013/002-A       Mark Brown
 4      B      Qte 2013/002-B       Mark Brown

There is nothing directly indicating that ids 2,3,4 belong together. Code refers to department.

I want to separate unique and 'compound' records such as Qte 2013/002-A/B and Qte 2013/001-A. Basically to get one quote_no per unique numeric quote_no. Any ideas would be greatly appreciated.

Upvotes: 0

Views: 66

Answers (1)

Abhishek Pathak
Abhishek Pathak

Reputation: 1569

Assuming the quote_no has a fixed format, you can GROUP() on a substring of the quote_no and get all the records with their respective counts.

From there, it should take a JOIN with the original table to remove all the duplicate rows, with the WHERE condition of (code LIKE '/' or count =1) to account for cherrypicking compound and unique records respectively.

SQLfiddle demo.

Upvotes: 2

Related Questions