用条件从两个表中选择concat

Question

我有两个表突变和引用如下:

## 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    |

对于表突变中的每一行，如果列pos与表引用中的pos1或pos2或pos3匹配，则应更新表突变中的alt_codon列，如下所示:

• 如果突变。Pos =引用。pos1然后将C1替换为ALT，给出alt_codon = ALT + C2 + C3
• 如果突变。Pos =引用。pos2然后C2替换为ALT，给出alt_codon = C1 + ALT + C3
• 如果突变。Pos =引用。pos3然后将C1替换为ALT，给出alt_codon = C1 + C2 + ALT

例如在突变的第一行，pos = 2786，它等于参考中的第3位pos3，对于alt_codon应该是c1 + c2 + alt = GTT

我认为我应该使用带有别名的UNION语句，我只是想不出一种方法来做到这一点，并编写了一段不工作的查询:

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;

任何帮助或建议将不胜感激:)

Answer 1

我建议您在更新查询中分别将突变表与参考表连接3次，这样您可以使用三种情况，在下面我已经更新了突变表的一些数据，以便产生所需的结果以匹配id与参考表

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`);

<<h2>看到演示/h2>