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?

  • 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. – Rick James Dec 20 '16 at 19:51
  • REPLACE is DELETE + INSERT. – Rick James Dec 20 '16 at 19:51
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 | |
  • You can replace the CASE expression with COALESCE(t1.value, t2.value) – Lennart Dec 19 '16 at 21:30
  • agree, as variant – a_vlad Dec 19 '16 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 | |
  • 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 – a_vlad Dec 19 '16 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. – Lennart Dec 19 '16 at 21:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.