CODEWITHSUNDEEP
mysqlsqlmergerecords
Noah Yamen
Noah Yamen

Reputation: 137

SQL how to merge similar records into single row from same table?

I'm currently trying to clean up a database of mailing subscribers that a former employee created. I've been able to consolidate and fix most issues (primarily duplicates), but I have instances of subscribers with duplicate records because they are subscribed to multiple regions. What I want to do is merge those duplicate records into 1.

Here is an redacted actual example of a duplicate record that I'd like to merge:

id     first     last    address    truck    machinery    gl    ne    nw
------------------------------------------------------------------------
1      Chuck     G....   12 Lorem   1                     1
2      Chuck     G....   12 Lorem            1                        1
3      Chuck     G....   12 Lorem            1                  1

And I'd like to merge the 2 into 1 record, and delete all duplicates (some have up to 9 duplicates) like this:

id     first     last    address    truck    machinery    gl    ne    nw
------------------------------------------------------------------------
1      Chuck     G....   12 Lorem   1        1            1     1     1

Upvotes: 9

Views: 15959

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Use Group By and Max/Min Aggregate

SELECT id, 
       first, 
       last, 
       address, 
       Max(truck)     AS truck, 
       Max(machinery) AS machinery, 
       Max(gl)        AS gl, 
       Max(ne)        AS ne, 
       Max(nw)        AS nw 
FROM   yourtable 
GROUP  BY id, 
          first, 
          last, 
          address

Upvotes: 9

Related Questions