mysql根据另一个查询的结果更新查询集

owfi6suc 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(339)

我被这个困住了。。我问了一个问题。。。

SELECT ID_Rollo, ID_Rollo_Original
FROM 
    interpretes 
    LEFT JOIN (
        SELECT ID_Rollo_Original, ID_Rollo, count(ID_Rollo) as count
        FROM rollooriginal 
        GROUP BY rollooriginal.ID_Rollo
        HAVING count = 1
    ) rollooriginal ON  interpretes.ID_Programa = rollooriginal.ID_Rollo
WHERE 
interpretes.ID_Prog IS NULL AND ID_Rollo IS NOT NULL

返回如下内容：

ID_Rollo:  ID_Rollo_Original
78 ------- 656
88 ------- 5012

另一方面，我有一个表与一些科尔口译员。

ID_table: ID_Programa: ID_Prog: etc: etc:
1 ------- 78 -------- NULL ------
2 ------- 88 -------- NULL ------
3 ------- 642 ------- 2108 ------
etc....

在某些情况下，id\u prog为null，我需要做的是创建查询来更新表解释，如果id\u prog为null，则将id\u rollo\u original的结果复制到id\u prog。。
注意，来自查询的id\u rollo和来自解释器的id\u programa是相等的。
预期

ID_table: ID_rollo: ID_Prog: etc: etc:
1 ------- 78 ------ 656 ------
2 ------- 88 ------ 5012 ------

像这样的。。。
update interpreverses set interpreverses.id\u prog=（id\u rollo\u original），其中interpreverses.id\u programa=（result id\u rollo）和interpreverses.id\u prog为空

mysql subquery sql-update

来源：https://stackoverflow.com/questions/48120136/mysql-update-query-set-from-the-result-of-another-query

1条答案

按热度按时间

2ledvvac1#

可能正在对子查询使用联接

UPDATE interpretes  a 
INNER  JOIN (
    SELECT ID_Rollo, ID_Rollo_Original
    FROM interpretes 
    LEFT JOIN (
            SELECT ID_Rollo_Original, ID_Rollo, count(ID_Rollo) as count
            FROM rollooriginal 
            GROUP BY rollooriginal.ID_Rollo
            HAVING count = 1
        ) rollooriginal ON  interpretes.ID_Programa = rollooriginal.ID_Rollo
    WHERE interpretes.ID_Prog IS NULL AND ID_Rollo IS NOT NULL
  ) T on t.ID_ROLLO = a.ID_ROLLO 
  SET a.ID_Prog = t.ID_Rollo_Original
  WHERE a.ID_Prog is NULL

赞(0）回复(0）举报 2021-06-21

我来回答

mysql根据另一个查询的结果更新查询集

1条答案

相关问题

热门标签

最新问答