We help IT Professionals succeed at work.

msql merge

mikesung99
mikesung99 asked
on
Hi,

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.

Thanks.





Comment
Watch Question

Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
I think you want  to read this article:
http://www.experts-exchange.com/A_3203.html

please clarify the db engine/version you are using ...
Nem SchlechtIT Supervisor
Top Expert 2009
Commented:
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
GROUP BY F1
;

Open in new window


Any string should be "higher" than a NULL or blank entry.

Author

Commented:
Thanks