为什么此MySQL更新无法正常工作? [英] Why doesn't this MySQL update work?

问题描述

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

have a table called meterreadings.  I need to copy the attribute meterreading from one row to the other.

以下是数据示例:

 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.

So basically, I want to copy all of the meterreading values based on the  meterreadingtype_id column so that meterreadingtype_id value of 2 will copy over to 1.  

完成后数据应如下所示:

The data should look like this when done:

 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.

Notice I copied the 500 over because the meterreadingtype_id was a 1 and I go the value from the next row because it was a 2.

这是我正在使用的查询:

Here is the query I am using:

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表.

We are using MySQL 5 and InnoDB tables if that helps.

推荐答案

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

The query is failing because you cannot modify a table and select from that same table in a subquery.

请参见子查询语法

一个限制是子查询的外部语句必须是以下之一:SELECT，INSERT，UPDATE，DELETE，SET或DO.另一个限制是当前您不能修改表并在子查询中从同一表中选择.这适用于诸如DELETE，INSERT，REPLACE，UPDATE和LOAD DATA INFILE之类的语句(因为可以在SET子句中使用子查询).

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欺骗来解决此问题:

I think you can get around this with some JOIN trickery:

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期间不会更改.这样做的副作用是，如果子查询的结果很大，那么您将消耗大量(或用完)内存.

I'm no MySQL expert, but I believe this works because MySQL processes the subquery first and stores the result in memory as a temporary table, which doesn't change during the UPDATE. A side effect of this is that if the result of the subquery is large, then you'll chew up a ton of (or run out of) memory.

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

The only way (as far as I know) to get around the memory issue is to pare down the subquery using criteria that aren't directly related to the update target. For example, if you were to do these updates as part of a nightly process, make the inner SELECT only return rows created in the past ~24 hours.

这篇关于为什么此MySQL更新无法正常工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！