mysql - 使用更新和连接时未知列，即使该列存在

Question

我使用以下 sql 更新一个名为 userStats 的表，它针对我提供的 userId 的每个用户运行。

update userStats us
join (select
    sum(postStatus = 0) published,
    sum(postStatus = 1) inactive,
    sum(postStatus = 5) recalled,
    sum(postStatus = 6) deleted
from userData where userId = @x) d on d.userId = us.userId 
set
us.published = coalesce(d.published,0),
us.inactive = coalesce(d.inactive,0),
us.recalled = coalesce(d.recalled,0),
us.deleted = coalesce(d.deleted,0),
us.total = coalesce(d.published+d.inactive+d.recalled+d.deleted,0);

我一直遇到错误 Unknown column 'd.userId' in 'on clause' 即使该列存在于表中也是如此。我过去一直使用这个非常相同的东西而没有错误。

此外，关于 where 的使用，是在设置列之后需要的另一个 where，或者 where 内部是否足以仅更新 userId 我提供。它只更新我提供的 userId，但我很不自在地没有在 where 之后再使用一个 where。我需要像以前那样在外面的另一个地方吗？

us.deleted = coalesce(d.deleted,0),
us.total = coalesce(d.published+d.inactive+d.recalled+d.deleted,0) where userId =@x;

Answer 1

别名为 d 的表没有明确选择 userID 列，尽管您的 userData 表具有该值。

所以，你可能应该这样做:

(select
    userID,  --> This field has to be selected to use in the join condition
    sum(postStatus = 0) published,
    sum(postStatus = 1) inactive,
    sum(postStatus = 5) recalled,
    sum(postStatus = 6) deleted
from userData where userId = @x) d on d.userId = us.userId 
                                         ^
                                         |___ You were getting error here.