• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

msql merge


I have a database table which stores routing information but there are records which I would like to merge.

The schema is as follows
           F1 - VARCHAR(30)
           F2 - VARCHAR(40)
           F3 - VARCHAR(6)
           F4 - DATETIME
           F5 - VARCHAR(40)

An example of a record is as follows:-
         01302,12345,Y,2011-12-05 15:20:28,

The issue is that there may be another record as follows:-
          01302,,Y,2011-12-05 17:25:30,54321  

What we would like to do is somehow merge records, so that for the example above, we would have:-
          01302,12345,Y,2011-12-05 17:25:30,54321

I know that F4 is different but this doesn't matter so much - the main goal is to have records with the same F1 consoldated into 1 record with the F2 and F5 fields merged. In the database table that we've inherited, there is no more than 2 records with the same F1.

We were considering using a SQL command but unsure as to the command to use - any help on this would be greatly appreciated.


2 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think you want  to read this article:

please clarify the db engine/version you are using ...
nemws1Database AdministratorCommented:
Just use MAX (I did it on all fields for the heck of it) with a GROUP BY:
SELECT F1, MAX(F2) as F2, MAX(F3) AS F3, MAX(F4) AS F4, MAX(F5) AS F5
FROM table_to_merge_data

Open in new window

Any string should be "higher" than a NULL or blank entry.
mikesung99Author Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now