用子查询更新 mysql 查询

有人能看出下面的查询有什么问题吗？

当我运行它时，我得到:

# 1064-您的 SQL 语法有错误; 请检查与您的 MySQL 服务器版本对应的手册以获得正确的语法 接近第8行的‘ a where a

Update Competition
Set Competition.NumberOfTeams =
(
SELECT count(*) as NumberOfTeams
FROM PicksPoints
where UserCompetitionID is not NULL
group by CompetitionID
) a
where a.CompetitionID =  Competition.CompetitionID
157984 次浏览
小开
最佳答案

The main issue is that the inner query cannot be related to your where clause on the outer update statement, because the where filter applies first to the table being updated before the inner subquery even executes. The typical way to handle a situation like this is a multi-table update.

Update
Competition as C
inner join (
select CompetitionId, count(*) as NumberOfTeams
from PicksPoints as p
where UserCompetitionID is not NULL
group by CompetitionID
) as A on C.CompetitionID = A.CompetitionID
set C.NumberOfTeams = A.NumberOfTeams

Demo: http://www.sqlfiddle.com/#!2/a74f3/1

小开

Thanks, I didn't have the idea of an UPDATE with INNER JOIN.

In the original query, the mistake was to name the subquery, which must return a value and can't therefore be aliased.

UPDATE Competition
SET Competition.NumberOfTeams =
(SELECT count(*) -- no column alias
FROM PicksPoints
WHERE UserCompetitionID is not NULL
-- put the join condition INSIDE the subquery :
AND CompetitionID =  Competition.CompetitionID
group by CompetitionID
) -- no table alias

should do the trick for every record of Competition.

To be noticed :

The effect is NOT EXACTLY the same as the query proposed by mellamokb, which won't update Competition records with no corresponding PickPoints.

Since SELECT id, COUNT(*) GROUP BY id will only count for existing values of ids,

whereas a SELECT COUNT(*) will always return a value, being 0 if no records are selected.

This may, or may not, be a problem for you.

0-aware version of mellamokb query would be :

Update Competition as C
LEFT join (
select CompetitionId, count(*) as NumberOfTeams
from PicksPoints as p
where UserCompetitionID is not NULL
group by CompetitionID
) as A on C.CompetitionID = A.CompetitionID
set C.NumberOfTeams = IFNULL(A.NumberOfTeams, 0)

In other words, if no corresponding PickPoints are found, set Competition.NumberOfTeams to zero.

小开

For the impatient:

UPDATE target AS t
INNER JOIN (
SELECT s.id, COUNT(*) AS count
FROM source_grouped AS s
-- WHERE s.custom_condition IS (true)
GROUP BY s.id
) AS aggregate ON aggregate.id = t.id
SET t.count = aggregate.count

That's @mellamokb's answer, as above, reduced to the max.

小开

You can check your eav_attributes table to find the relevant attribute IDs for each image role, such as; eav_attributes

Then you can use those to set whichever role to any other role for all products like so;

UPDATE catalog_product_entity_varchar AS `v` INNER JOIN (SELECT `value`,`entity_id` FROM `catalog_product_entity_varchar` WHERE `attribute_id`=86) AS `j` ON `j`.`entity_id`=`v`.entity_id SET `v`.`value`=j.`value` WHERE `v`.attribute_id = 85 AND `v`.`entity_id`=`j`.`entity_id`

The above will set all your 'base' roles to the 'small' image of the same product.


提交答案