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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.