MySQL 结合 UPDATE 和 SELECT 查询 [英] MySQL combine UPDATE and SELECT query
本文介绍了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
:
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屋!
查看全文