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'),
(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?