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

Question

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: - Insert into A the values that has been added to B - Remove from A the records that have been removed from B - 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'),…

Accepted Answer

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)
```

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

Solution

Problem