从MySQL中的子查询中的更新表访问列 [英] Accessing column from update table in subquery in mysql

问题描述

我有2个表，位置和发生。出现会存储带有creatorId和时间戳的事件。我正在尝试在发生时间戳记的5分钟内，从位置表计算出所有位置的所有时间戳记的平均纬度，并将其放入发生纬度列。

I have 2 tables, "location" and "occurrence". occurrence stores events, with a creatorId and a timestamp. I'm trying to calculate an average latitude from the location table for all timestamps from location within 5 minutes of the occurrence timestamp, and put that in the occurrence latitude column.

我在下面尝试过，并在 where子句中获得未知列'occurrence.creatorId'。

I've tried the below and get an "unknown column 'occurrence.creatorId' in 'where clause'.

update occurrence set latitude = (
select avg(latitude) from (
select * from location where (
location.creatorId=occurrence.creatorId
and location.timestamp<occurrence.timestamp+interval 5 minute
and location.timestamp>occurrence.timestamp-interval 5 minute
)
) as test
);

我的怀疑是我试图像Java程序那样编写太多代码，有人可以帮助我的大脑变得更加MySQL化吗？

My suspicion is that I'm trying to write this too much like a Java program. Can someone help my brain become more MySQL-ified?

谢谢！
bugg

Thanks! bugg

推荐答案

尝试以下方法：

update o
set latitude = (select avg(latitude)
                from location
                where location.creatorId=o.creatorId
                and location.timestamp<o.timestamp+interval 5 minute
                and location.timestamp>o.timestamp-interval 5 minute)
from occurrence o

您收到错误未知列'occurrence.creatorId'，因为表最内层的子查询无法访问该事件。结果，对子查询中出现表中列的所有引用均无效。上面的查询应该可以。

You were getting the error "unknown column 'occurrence.creatorId'" because, table occurrence is not accessible to your innermost subquery. As a result all references to columns in the occurrence table from the subquery are invalid. Above query should work ok.

这篇关于从MySQL中的子查询中的更新表访问列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！