用更新替换select表

Question

我有一张中间桌：

text_mining_molecule

|text_mining_id| molecule_id |
| -------------| ----------  |
|  ID          |  ID         |

和另外两张表格：

表Molécules:

id  | main_name | others …
--- | --------- | ------
1   | caféine   | others …

Table jsonTextMining:

id | title  | molecule_name                       | others …
---|------- |-------------------------------------|------
1  | title1 | colchicine, cellulose, acid, caféine| others …

在从其他两个表中选择ID的列表中选择text_mining_molecule时，需要插入json_text_mining和molecule。

实际上，当选择分数低于4时，有一个下拉列表，它已经插入了从json_text_mining到text_mining的所有行。

INSERT INTO text_mining (id, solrid, originalpaper, annotatedfile, title, keyword, importantsentence, version, klimischscore, moleculename, synonymname, validation)
            SELECT id, solrid, originalpaper, annotatedfile, title, keyword, importantsentence, version, klimischscore, molecule_name, synonym_name, validation
            FROM json_text_mining WHERE klimischscore < 4

这是可行的，但是我需要用相关的ID填充text_mining_molecule，所以我也有这部分代码：

SELECT s.id, m.id
          FROM (SELECT id, regexp_split_to_table(molecule_name,', ') AS m_name
          FROM json_text_mining) AS s, molecule m
          WHERE m.main_name = s.m_name;

如何使用text_mining_molecule而不是select直接更新select表？

Answer 1

使用CTE。例如，如果text_mining_molecule.molecule引用molecule.id，那就像：

with c as (
SELECT s.id sid, m.id mid
          FROM (SELECT id, regexp_split_to_table(molecule_name,', ') AS m_name
          FROM json_text_mining) AS s, molecule m
          WHERE m.main_name = s.m_name
)
update text_mining_molecule t
set sid = c.sid
from c
where t.molecule = c.mid