MySQL 结合 UPDATE 和 SELECT 查询

问题描述：

我有以下 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:

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 查询

相关推荐