SELECT concat from two tables with condition

Question

I have two tables mutation and reference as below:

## Mutation
mysql> select * from mutation limit 10;
+------+------+------+------+-----------+
| pos  | ref  | alt  | name | alt_codon |
+------+------+------+------+-----------+
| 6    | C    | T    | ND1  | NULL      |
| 10   | T    | C    | ND1  | NULL      |
| 2799 | A    | G    | ND1  | NULL      |
| 2808 | C    | T    | ND1  | NULL      |
| 2825 | T    | C    | ND1  | NULL      |
| 2847 | A    | G    | ND1  | NULL      |

## Reference
mysql> select * from reference limit 10;
+------+------+------+----------+------+------+------+
| pos1 | pos2 | pos3 | codon    | c1   | c2   | c3   |
+------+------+------+----------+------+------+------+
|    1 |    2 |    3 | TTC      | T    | T    | C    |
|    4 |    5 |    6 | GTC      | G    | T    | C    |

For each line in the table mutation, if the column pos is a match for a pos1 or pos2 or pos3 in the table reference that should update the column alt_codon in table mutation as follow:

• If mutation.pos = reference.pos1 THEN C1 is replaced by ALT, giving alt_codon = ALT + C2 + C3
• If mutation.pos = reference.pos2 THEN C2 is replaced by ALT, giving alt_codon = C1 + ALT + C3
• If mutation.pos = reference.pos3 THEN C1 is replaced by ALT, giving alt_codon = C1 + C2 + ALT

For example on the first row of mutation, pos = 2786, which is equal to pos3, 3rd in reference, to alt_codon should be c1 + c2 + alt = GTT

I figured that I should use a UNION statement with alias, I just can't figure out a way to do this, and wrote a piece of query that doesn't work:

UPDATE mutation CROSS JOIN reference ON
(mutation.pos = reference.pos1 OR mutation.pos = reference.po2 OR mutation.pos = reference.pos3)
SET mutation.alt_codon = 
CASE WHEN mutation.pos = reference.pos1 THEN (SELECT CONCAT(mutation.alt, reference.c2, reference.c3))
WHEN mutation.pos = reference.pos2 THEN (SELECT CONCAT(reference.c1, mutation.alt, reference.c3))
WHEN mutation.pos = reference.pos3 THEN (SELECT CONCAT(reference.c1, reference.c2, mutation.alt))
ELSE mutation.alt_codon END;

Any help or suggestion will be greatly appreciated :)

Answer 1

One way what i suggest you to join mutation table with reference table 3 times individually in update queries one by one this way you can use you three scenario cases ,in below fiddle i have updated some data for mutation table in case to produce desired results to match ids with pos1,2 and 3 with reference table

update Mutation m
join Reference r on(m.`pos` = r.`pos3`)
set m.`alt_codon` = CONCAT(r.`c1`, r.`c2`,m.`alt`);

update Mutation m
join Reference r on(m.`pos` = r.`pos2`)
set m.`alt_codon` = CONCAT(r.`c1`,m.`alt`, r.`c3`);

update Mutation m
join Reference r on(m.`pos` = r.`pos1`)
set m.`alt_codon` = CONCAT(m.`alt`, r.`c2`,r.`c3`);

See Demo