Geek Answers Handbook

How to synchronize two MySQL tables that have different structures?

I move from one system to another, and in the process I will start both systems at the same time. I should be able to synchronize unidirectionally from one table to another, while preserving the primary keys of each table.

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 in Table B, and there are several fields in B that will not be synchronized.

How can I do the following:

  • Paste in the values ​​that were added to B
  • Delete from A records deleted from B
  • Update A with changed fields from B

Here are 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'm going to right now is to create a joined table between two tables and build queries accordingly. For instance:

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

What do you suggest?

+4
source share
3 answers

1. Paste into 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 modified fields from B

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

I built a stored procedure for merging correctly. Thanks for answers. This is what I will go 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
+3
source 
 TRUNCATE TABLE A; INSERT INTO A (id1, match1, value1) SELECT id2, match2, value2 FROM B;
0
source

More articles:


All Articles