问题描述
有一个名为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.
请参见子查询语法
我认为您可以通过一些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更新无法正常工作?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!