MySQL combine UPDATE and SELECT query

问题:

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;

Example data returned:

  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

I want to combine this with the following UPDATE statement

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

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

Thanks.

回答1:

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;

回答2:

UPDATE  performers
SET performers.RawAliasCount = count_table.CountOfAliases, performers.RawDealCount = count_table.CountOfDeals
FROM   performers
        INNER 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
        ) count_table
ON count_table.ID = performers.ID;

When this type of question is asked, thank you to put the tables schema.

edit : sorry, it's sql-server syntax.