mysql - 为什么这个 MySQL 更新不起作用？

Question

有一个名为meterreadings的表。我需要将属性 meterreading 从一行复制到另一行。

以下是数据示例:

 id   |  meterreadingdate  |  meterreading  |  meterreadingtype_id | created
-------------------------------------------------------------------------------
 1    |  2011-10-01        |  0             |  1                   | 2011-10-15
 2    |  2011-10-01        |  500           |  2                   | 2011-10-15

基本上，我想根据 meterreadingtype_id 列复制所有 meterreading 值，以便将 meterreadingtype_id 值 2 复制过来到 1。

完成后数据应如下所示:

 id   |  meterreadingdate  |  meterreading  |  meterreadingtype_id | created
-------------------------------------------------------------------------------
 1    |  2011-10-01        |  500           |  1                   | 2011-10-15
 2    |  2011-10-01        |  500           |  2                   | 2011-10-15

请注意，我复制了 500，因为 meterreadingtype_id 是 1，而我从下一行获取值，因为它是 2。

这是我正在使用的查询:

update meterreadings mr1
set meterreading = 
(
    select
        meterreading 
    from meterreadings
    where
        meterreadingtype_id = 2
        and meterreadingdate = mr1.meterreadingdate
        and location_id = mr1.location_id
        and created = mr1.created
        and asset_id = mr1.asset_id
)
where asset_id in (select id from assets where model_id in (select id from models where make_id IN (81, 82)))
and meterreadingtype_id = 1

如果有帮助的话，我们正在使用 MySQL 5 和 InnoDB 表。

Answer 1

查询失败，因为您无法修改表并在子查询中从同一个表中进行选择。

参见Subquery Syntax

One restriction is that a subquery's outer statement must be one of: SELECT, INSERT, UPDATE, DELETE, SET, or DO. Another restriction is that currently you cannot modify a table and select from the same table in a subquery. This applies to statements such as DELETE, INSERT, REPLACE, UPDATE, and (because subqueries can be used in the SET clause) LOAD DATA INFILE.

我认为你可以通过一些 JOIN 技巧来解决这个问题:

UPDATE meterreadings AS tgt
INNER JOIN (
  SELECT * FROM meterreadings
  WHERE meterreadingtype_id = 2
  ) AS src
ON tgt.meterreadingdate = src.meterreadingdate
   AND tgt.location_id = src.location_id
   AND tgt.created = src.created
   AND tgt.asset_id = src.asset_id
SET tgt.meterreading = src.meterreading

我不是 MySQL 专家，但我相信这是可行的，因为 MySQL 首先处理子查询并将结果作为临时表存储在内存中，在 UPDATE 期间不会更改。这样做的副作用是，如果子查询的结果很大，那么您将消耗大量(或耗尽)内存。

(据我所知)解决内存问题的唯一方法是使用与更新目标不直接相关的条件来减少子查询。例如，如果您要将这些更新作为夜间流程的一部分进行，请使内部 SELECT 仅返回过去约 24 小时内创建的行。