I've got data with measurements split between two different tables, but the data doesn't need to be present in both:
mysql> SELECT * FROM t1; mysql> SELECT * FROM t2;
+----------------+-------+ +----------------+-------+
| measurement_id | value | | measurement_id | value |
+----------------+-------+ +----------------+-------+
| 1 | data1 | | 1 | NULL |
| 2 | data1 | | 2 | data2 |
| 3 | NULL | | 4 | data2 |
| 5 | data1 | | 8 | data2 |
| 8 | data1 | | 9 | data2 |
| 9 | NULL | +----------------+-------+
+----------------+-------+
My users need to be able to combine the two tables into a third table, depending on their choice of whether data1 should take precedence over data2, whether NULL data should be copied, etc.
An example of one such possible user-requirement combination is "fill t3
with data from t1
unless there is non-NULL
data from t2
to replace NULL
or non-existing data from t1
." The resulting table would be
mysql> SELECT * FROM t3;
+----------------+-------+
| measurement_id | value |
+----------------+-------+
| 1 | data1 |
| 2 | data1 |
| 3 | NULL |
| 4 | data2 |
| 5 | data1 |
| 8 | data1 |
| 9 | data2 |
+----------------+-------+
The way I implement that condition is the following SQL commands:
INSERT INTO t3 SELECT * FROM t1;
REPLACE INTO t3 SELECT * FROM t2 WHERE t2.measurement_id NOT IN (SELECT measurement_id FROM t1) OR t2.measurement_id IN (SELECT measurement_id FROM t1 WHERE value IS NULL);
There are other example, more simple or more complicated, I can give, but they all rely on first copying all data from one table into another, then doing a REPLACE INTO
to overwrite data on conditions.
My question is: How can I combine the above INSERT
and REPLACE INTO
commands into a single command so I'm not copying data from t1
that will just be replaced in the second command?
Best Answer
as variant:
as benefits - no REPLACE