0

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?

Improve this question
2
  • REPLACE needs for the target table to have a PRIMARY KEY and/or UNIQUE KEY(s). Let's see SHOW CREATE TABLE for both tables. Commented Dec 20, 2016 at 19:51
  • REPLACE is DELETE + INSERT. Commented Dec 20, 2016 at 19:51

2 Answers 2

Reset to default
1

as variant:

INSERT INTO t3 


SELECT t1.measurement_id,
CASE WHEN t1.value IS NULL THEN
     t2.value
ELSE 
     t1.value
END as value

FROM t1 LEFT JOIN t2 ON t2.measurement_id = t1.measurement_id AND t1.value IS NULL

UNION ALL

SELECT 
     t2.measurement_id,
     t2.value 
FROM t2 WHERE t2.measurement_id NOT IN (SELECT measurement_id FROM t1)

as benefits - no REPLACE

Improve this answer
2
  • You can replace the CASE expression with COALESCE(t1.value, t2.value) Commented Dec 19, 2016 at 21:30
  • agree, as variant Commented Dec 19, 2016 at 21:38
1

Not sure I understould you correctly but you can pick all rows from T1 where there is not a row from T2 that matches:

SELECT measurement_id, value
FROM t1
WHERE NOT EXISTS (
    SELECT 1 
    FROM T2
    WHERE t1.measurement_id = t2.measurement_id
      AND t2.value IS NOT NULL
);

Second step is to add the rows from T2:

SELECT measurement_id, value
FROM t2
WHERE t2.value IS NOT NULL;

You can combine these queries into one query using a UNION. Since the result from query1 and query2 will be disjoint you can safely use UNION ALL (for most DBMS UNION ALL is more efficient than UNION), i.e.:

INSERT INTO t3 (measurement_id, value)
SELECT measurement_id, value
FROM t1
WHERE NOT EXISTS (
    SELECT 1 
    FROM T2
    WHERE t1.measurement_id = t2.measurement_id
      AND t2.value IS NOT NULL
)
UNION ALL
SELECT measurement_id, value
FROM t2
WHERE t2.value IS NOT NULL;
Improve this answer
2
  • check please one moment - as I understand question - not important what is t2.value, important t1.value NULL or NOT and this is not checked in You variant Commented Dec 19, 2016 at 21:31
  • You are probably right that t1.value should be checked for NULL, but the OP also states that "fill t3 with data from t1 unless there is non-NULL data from t2 to replace NULL", so whether t2.value is NULL or NOT is also important. I'll await a comment from the OP before I change my answer. Both our answers will be easily adopted to whatever combination of NULL that is valid. Commented Dec 19, 2016 at 21:42

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.