0

I have three tables which has the same schema. all of them has 10000000+ rows and has unique data as well as duplicate keys but with a datetime column can differentiate them and get the newest row.

I want to create a one combine table with all the data in my three tables updated with the newest records for duplicate values.

Suggest me a method to achieve this.

0

The UNION command should be your friend: http://dev.mysql.com/doc/refman/5.1/en/union.html

With UNION you can combine several SELECT results into one result. All selects must have the same field structure. In the MySQL docu there is a good example for your problem:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

You create a select for each of your tables with all filter and sorting you need and combine them with UNION to one select. The result of it can be stored by INSERT INTO ... SELECT ... in an archive table. You can also store this query in a MySQL view to have live data of all three tables with one select - Depending on the execution time / indexes etc.

  • 1
    With mysql union I cannot specify which field I need to distinct with. It only works for the whole row. so it will select duplicate keys as the datetime is different. am I right? – ivcode Sep 5 '14 at 6:49
  • Could you please give more details what you try to get? I understand it as you need the topmost entry of each table. Your actual queries to get the data would be great. – Rossi Sep 5 '14 at 13:59
0

If I understand correctly. . . Could you try something like this maybe? I don't know if it is the most optimal way of doing it wih your quantity of data though:

You have three tables with the same schema and data, diffentiated by timestamp:

CREATE TABLE `table1` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`dataName` varchar(45) DEFAULT NULL,`timeField` timestamp NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `table2` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`dataName` varchar(45) DEFAULT NULL,`timeField` timestamp NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `table3` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`dataName` varchar(45) DEFAULT NULL,`timeField` timestamp NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `Table1` (`dataName`) VALUES ('aaa'),('bbb'),('ccc'),('ddd'),('eee');
wait 5 seconds
INSERT INTO `Table2` (`dataName`) VALUES ('aaa'),('bbb'),('ccc'),('ddd'),('eee');
wait 5 seconds
INSERT INTO `Table3` (`dataName`) VALUES ('aaa'),('bbb'),('ccc'),('ddd'),('eee');

Create a table to hold all your data from all three tables. This has the same schema as above, but has no Unique indexes (you may want to add indexes to yours as long as they are not Unique)

CREATE TABLE `table4` (`id` int(10) unsigned NOT NULL,`dataName` varchar(45) DEFAULT NULL,`timeField` timestamp NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Next Insert your data with a UNION statment (as per Rossi's answer above)

INSERT INTO `table4`
SELECT * FROM (
 SELECT * FROM `Table1` t1
 UNION ALL
 SELECT * FROM `Table2` t2
 UNION ALL 
 SELECT * FROM `Table3` t3
) AS t4;

Finally you create your final table to hold the data and insert just the latest entry per item (in this case id)

CREATE TABLE `table5` (`id` int(10) unsigned NOT NULL,`dataName` varchar(45) DEFAULT NULL,`timeField` timestamp NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `table5`
SELECT * FROM 
    (
        SELECT * FROM `table4`
        ORDER BY `timeField` DESC
    ) sub
GROUP BY `id`;

So if you do:

SELECT * FROM `table5`;

you should see one entry per item, with just the most recent tiemstamp

If you wanted you could even combine it into a single query as:

SELECT * FROM 
    (
        SELECT * FROM (
            SELECT * FROM (
                SELECT * FROM `Table1` t1
                UNION ALL
                SELECT * FROM `Table2` t2
                UNION ALL 
                SELECT * FROM `Table3` t3
                ) AS t4
            ORDER BY `timeField` DESC
        ) sub
    ) sub2
GROUP BY `id`;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.