Mysql "safe" merge

Question

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?

Paul

Answer 1

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';