mysql merge duplicate records in table

Question

how can i get final table? I want to merge so that if it compares and there is an empty column in a record, it fills it up with the latter value. The rules are that storetype should appear only once for each user

enter image description here

J A · Answer 1 · 2012-05-28T18:44:25.340

This is probably the dirtiest piece of code I've ever done :)... and I won't even recommend it if you have more than few hundreds of records to process. Let me know as I wasn't able to check it on IDE.

<?php

  $query = 'SELECT * FROM TABLE 1';
  $result = mysql_query($query);
  if ($result){
      while ($result=mysql_fetch_array($result)){
         $data[] = $row;
     }
  }

  // 

  if (isset($data)){
     $x=0;
     $y=0;
     $missingColumn = array();

     while ($x < count($data)){
        foreach ($data[$x] as $key=>$value){
            if (empty($value)){
               $missingColumn[$y][] = $key;
            } else {
               $output[$y][] = $value;
            }  
        }
        if ($missingColumn[$y]){
           $rowComplete = FALSE;
           $yetToBeCompleted = count($missingColumn[$y]);
           while (!$rowComplete && $x<count($data)){
              $x++;
              foreach ($missingColumn as $value){
                 if ($data[$x][$value] <> ''){
                    $output[$y][$value] = $data[$x][$value];
                    $yetToBeCompleted--;
                 }  
              }
              if (0 == $yetToBeCompleted){
                 $y++;
                 break;
                 //$rowComplete = TRUE;
              }
           }
           $y++;          

        } else {
          $y++;
          $x++;
        }
     }
  }


?>

thanks. can i get a table 'final' created and populated as shown? — karto, May 28 '12 at 17:59
If you mean eliminating columns with empty value, that shouldn't be called "merged". how about the query above? — J A, May 28 '12 at 17:59
You can Add SELECT DISTINCT filename, date from table1 to get unique records. — J A, May 28 '12 at 18:00

score 0 · Answer 2 · answered May 28 '12 at 17:54

0

Maybe I'm not that versatile in mysql but without any difficult data alteration techniques, I don't think that's possible.

Edit especially because in some coloums you take the most encountered value and replace the "merged" cell with that value

answered May 28 '12 at 17:54

Florin Stingaciu

8,085
2
24
45

merge and delete duplicate records – karto May 28 '12 at 17:56
You keep saying that, but how do you plan on merging them. What are you rules? It seems to me that you always have a sterotype? Maybe somehow figure out how to group by name and storetype and from that point grab columns as they come. – Florin Stingaciu May 28 '12 at 18:00
The rules are that storetype should appear only once – karto May 28 '12 at 18:02
You should check out this thread. It maybe useful. http://stackoverflow.com/questions/2941116/merge-two-rows-in-sql – Florin Stingaciu May 28 '12 at 18:06

score 0 · Accepted Answer · answered May 28 '12 at 18:29

0

It looks like you're grouping by username and storetype. Try running this:

select min(id), username, min(filename), min(date), min(desc), storetype,
    min(password), min(email), min(ftp)
from table1
group by username, storetype

Note, of course, this will work in the case you really have the data as you displayed in your screenshot, i.e., only 1 non-null value per each username and storetype pair in each column but the id column. In that case, the id column, it looks like you chose the minimum, so the query will result in that too.

answered May 28 '12 at 18:29

Mosty Mostacho

42,742
16
96
123

thanks. working at it now. How do i put the results in another table(Final)? – karto May 28 '12 at 19:07
@karto There are many questions with info on how to do that, such as [this one](http://stackoverflow.com/questions/1304750/insert-subquery-in-select-query) – Mosty Mostacho May 28 '12 at 19:12
getting happy with this.:-) now seeing the query nicely displayed. Now i will look at how to populate it in a new table – karto May 28 '12 at 19:22
@karto If this did the trick, then I wonder why you selected another answer as the correct one. – Mosty Mostacho May 29 '12 at 07:19
i had selected that earlier but realised this was more optimzed. thanks for the corrections. +1 – karto May 29 '12 at 17:05

mysql merge duplicate records in table

3 Answers3