Mysql "safe" merge

I have a table with incomplete field data in each of its rows that I want to fix.

Here's an what I mean:

mysql> CREATE TABLE test (
       key INT
       a CHAR(1),
       b CHAR(1),
       c CHAR(1),
    );
mysql> CREATE TABLE test2 (
       key INT
       a CHAR(1),
       b CHAR(1),
       c CHAR(1),
    );
mysql> INSERT INTO test (1,'','','');
mysql> INSERT INTO test (2,'X','','');
mysql> INSERT INTO test (3,'','Y','');

mysql> INSERT INTO test2 (2,'X','','Z');
mysql> INSERT INTO test2 (4,'X','Y','Z');

I would like to non-destructively "merge" test2 data into test based on the 'key' field so that:

• if a field in test already has a non-empty value for a given key, it is retained.
• if a field in test has an empty value and the corresponding field in test2 is not empty, the value from test2 replaces the corresponding field in test for a given key.
• if the fields in test and test2 are both non-empty and differ, do nothing.

After the "开发者_运维技巧merge" I'd like the 'test' table data to look like:

(1,'','','');
(2,'X','','Z');
(3,'','Y','');
(4,'X','Y','Z');

Kind of like filling in the holes in the test table without clobbering anything.

Can anyone think of a way to do this?

MySQL has a special feature for this case :)

INSERT INTO test2 VALUES (2, 'X', '', 'Z')
ON DUPLICATE KEY UPDATE a='X', c='Z';
INSERT INTO test2 VALUES (4, 'X', 'Y', 'Z')
ON DUPLICATE KEY UPDATE a='X', b='Y', d='Z';