2

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

  • You could use UNION ALL and GROUP BY the PK. – oNare Jul 17 '15 at 13:03
4

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)
;
  • Yes second option is way better. Actually I was using this method, but your ideal of datetime column sounds much better and cleaner on sql side – ivcode Jul 21 '15 at 5:00
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
  • Your method seems ok but it's three joins on very large tables – ivcode Jul 21 '15 at 5:04
  • Yes, I was not aware of feature "ON DUPLICATE KEY UPDATE " This sounds promising. – Aneesh Mon N Jul 21 '15 at 6:04
0

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

enter image description here

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

Hope this helps !!

  • It should be a command line option, because I'm on redhat and it's suppose to be on a script – ivcode Jul 17 '15 at 10:15

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.