How to merge two large mysql tables into one with similar data keeping the latest record

Question

I have two similar large tables with below table structure

CREATE TABLE tableA (
  col1 varchar(20) NOT NULL,
  col2 varchar(40) NOT NULL,
  date date NOT NULL,
  time time NOT NULL,
  PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE tableB (
  col1 varchar(20) NOT NULL,
  col2 varchar(40) NOT NULL,
  date date NOT NULL,
  time time NOT NULL,
  PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I want to create one large table merging above two tables with the latest record

CREATE TABLE combine_table (
  col1 varchar(20) NOT NULL,
  col2 varchar(40) NOT NULL,
  date date NOT NULL,
  time time NOT NULL,
  PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Some data may duplicated in table A and B. How can I achieve this. Note that these tables are very large

Answer 1

Since you have very much data at hand, I suggest you merge your date and time columns first. Then you can use an index efficiently. If you don't, you will have to do something like

...WHERE CONCAT(date, ' ', time) = SELECT MAX(CONCAT(date, ' ', time)) ...

So, first do this for both tables.

ALTER TABLE tableA ADD COLUMN creation_date datetime; /*or whatever name, just make it meaningful and don't use keywords*/
UPDATE tableA SET creation_date = CONCAT(date, ' ', time);
ALTER TABLE tableA DROP COLUMN date, DROP COLUMN time;
CREATE INDEX idx_dt_tableA_creation ON tableA(creation_date);

Then you can insert both tables into your combine_table (Note, left this for completeness, the second option is much better).

INSERT INTO combined_table
SELECT col1, col2, creation_date
FROM (
      SELECT col1, col2, creation_date 
      FROM tableA 
      UNION ALL
      SELECT col1, col2, creation_date 
      FROM tableB 
) sq /*subquery_alias*/
WHERE creation_date = (SELECT MAX(creation_date) FROM (
                                    SELECT col1, col2, creation_date 
                                    FROM tableA 
                                    UNION ALL
                                    SELECT col1, col2, creation_date 
                                    FROM tableB 
                        ) another_sq 
                        WHERE sq.col1 = another_sq.col1
                       )
;

Nonetheless, this will be a heavy operation, if you really have that much data.

Now that I think of it, there's a better way of doing it.

First insert tableA

INSERT INTO combined_table
SELECT * FROM tableA;

Then do an insert on duplicate key update.

INSERT INTO combined_table c
SELECT * FROM tableB b
ON DUPLICATE KEY UPDATE 
/*you can skip col1, since it's the identifying primary key here*/
col2 = IF(b.creation_date > c.creation_date, b.col2, c.col2),
creation_date = IF(b.creation_date > c.creation_date, b.creation_date, c.creation_date)
;

Answer 2

    --#1 Insert from A
    insert into combine_table
    select A.* from
    tableA A left join tableB B on a.col1=b.col1
    where b.col1 is null

    --#2 Insert from B
    insert into combine_table
    select B.* from
    tableB B left join tableA A on a.col1=b.col1
    where a.col1 is null

    --#3 Insert from A & B with the latest one
    insert into combine_table
    select r.col1,coalesce(a.col2,b.col2),r.date,r.time
    (select max(date) as date,max(time) as time,a.col1 from
    tableB B inner join tableA A on a.col1=b.col1
    group by a.col1) r
    left join tableA a on a.col1 = r.col1 and a.date=r.date and a.time = r.time
    left join tableB a on b.col1 = r.col1 and b.date=r.date and b.time = r.time

Answer 3

Use toad for MySQL 7.3 Freeware, there you will have option of sync

Put the 2 tables in 2 different databases, Make a data compare and then sync

Hope this helps !!