CODEWITHSUNDEEP
mysqlsqlsynchronization
Dooltaz
Dooltaz

Reputation: 2463

How can I synchronize two MySQL tables that have different structures?

I am migrating from one system to another and in the process, I will be running both systems concurrently. I need to be able to synchronize uni-directionally from one table to another while maintaining each table's primary keys.

In this example, I have two tables (A) and (B). I need to synchronize value1 and value2 (below) from table B to table A based on a common foreign key (match1 and match2 below). Table A will have additional fields than Table B, and there are some fields in B that will not be synchronized.

How can I do the following:

  1. Insert into A the values that has been added to B
  2. Remove from A the records that have been removed from B
  3. Update A with the changed fields from B

Here is some demo data:

DROP TABLE IF EXISTS `a`;
CREATE TABLE IF NOT EXISTS `a` (
  `id1` int(10) unsigned NOT NULL,
  `match1` int(10) unsigned NOT NULL,
  `value1` varchar(255) NOT NULL,
  PRIMARY KEY  (`id1`)
);

INSERT INTO `a` (`id1`, `match1`, `value1`) VALUES
(1, 1, 'A'),
(2, 2, 'A'),
(3, 3, 'B'),
(4, 4, 'C'),
(5, 5, 'C');

DROP TABLE IF EXISTS `b`;
CREATE TABLE IF NOT EXISTS `b` (
  `id2` int(10) unsigned NOT NULL,
  `match2` int(10) unsigned NOT NULL,
  `value2` varchar(255) NOT NULL,
  PRIMARY KEY  (`id2`)
);

INSERT INTO `b` (`id2`, `match2`, `value2`) VALUES
(1, 1, 'A'),
(2, 2, 'A'),
(3, 3, 'B'),
(4, 5, 'D'),
(5, 6, 'D'),
(6, 7, 'F');

The direction I am currently going is to create a merged table between the two tables, and build the queries accordingly. For example:

create table ab as (select * from a, b);

What do you suggest?

Upvotes: 5

Views: 9328

Answers (3)

Dooltaz
Dooltaz

Reputation: 2463

I built a stored procedure to merge everything correctly. Thanks for the responses. This is what I will be going with.

BEGIN

DECLARE loop_done INT;
DECLARE orphan varchar(255);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET loop_done=1;

INSERT INTO a(id1, match1, value1)
SELECT '', match2, value2
FROM b WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.match1 = b.match2);

UPDATE a SET value1 = (SELECT value2 FROM b WHERE a.match1 = b.match2);

/* This Delete statement works better for MySQL. */
SET loop_done=0;
SET orphan=null;
SELECT id1 INTO orphan FROM a left join b on (b.match2 = a.match1) where b.id2 is null LIMIT 1;
WHILE (loop_done=0) DO
 DELETE FROM a WHERE id1=orphan;
 SELECT id1 INTO orphan FROM a left join b on (b.match2 = a.match1) where b.id2 is null LIMIT 1;
END WHILE;

END

Upvotes: 3

MBillock
MBillock

Reputation: 220

TRUNCATE TABLE A;

INSERT INTO A (id1, match1, value1)
    SELECT id2, match2, value2 FROM B;

Upvotes: 0

Lukasz Lysik
Lukasz Lysik

Reputation: 10630

1.Insert into A everything that has been added to B

INSERT INTO a(id1, match1, value1)
SELECT id2, match2, value2
FROM b WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.match1 = b.match2)

2.Remove from A everything that has been removed from B

DELETE FROM a
WHERE match1 IN (SELECT match1 FROM a INNER JOIN B ON A.match1 = B.match2)

3.Update A with the changed fields from B

UPDATE a
SET value1 = (SELECT value2 FROM b WHERE a.match1 = b.match2)

Upvotes: 4

Related Questions