MySQL 结合 UPDATE 和 SELECT 查询 [英] MySQL combine UPDATE and SELECT query

问题描述

我有以下 SELECT 语句返回数据，示例如下:

I have the following SELECT statement that returns data, example below:

SELECT performers.ID, 
       performers.Name, 
       COUNT(*) AS CountOfDeals, 
       COUNT(DISTINCT(deals.Name)) AS CountOfAliases 
  FROM deals RIGHT JOIN performers 
               ON deals.name LIKE CONCAT('%', performers.name, '%') 
 WHERE performers.ID IN ( 27952, 27951, 27950, 27949, 27948 ) 
 GROUP BY Name;

返回的示例数据:

  ID        Name             CountOfDeals     CountOfAliases
27952   Christine Hoberg          1                 0
27951   Indian Jewelry            1                 0
27952   Kinky Friedman            5                 3
27949   KJ-52                     1                 0
27960   River Whyless             1                 0

我想把它和下面的 UPDATE 语句结合起来

I want to combine this with the following UPDATE statement

UPDATE performers 
   SET RawAliasCount = CountOfAliases, 
       RawDealCount = CountOfDeals 
 WHERE ID = ?

运行更新语句所需的所有值都在上面的 select 语句中返回，所以希望这应该很容易.

All the values needed to run the update statement are returned in the select statement above so hopefully this should be pretty easy.

谢谢.

推荐答案

Use update with join:

Use update with join:

UPDATE performers p JOIN
       (SELECT performers.ID, performers.Name, COUNT(*) AS CountOfDeals, 
               COUNT(DISTINCT(deals.Name)) AS CountOfAliases 
        FROM deals RIGHT JOIN
             performers
             on deals.name LIKE CONCAT('%', performers.name, '%') 
        WHERE performers.ID IN (27952, 27951, 27950, 27949, 27948) 
        GROUP BY Name
       ) pp
       ON pp.id = p.id
    SET RawAliasCount = pp.CountOfAliases,
        RawDealCount = pp.CountOfDeals;

这篇关于MySQL 结合 UPDATE 和 SELECT 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！