MYSQL-如何为group by select语句产生的所有项目更新字段 [英] MYSQL - how to update a field for all items resulting from group by select statement
本文介绍了MYSQL-如何为group by select语句产生的所有项目更新字段的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个这样的选择:
SELECT field1, field2, field3
FROM table WHERE field1= 5 AND field_flag =1
GROUP BY field1, field2, field3 limit 1000;
我想为结果行更新field_flag.如何在MySQL中做到这一点?
I want to update field_flag for the resulting rows. How can I do that in MySQL?
推荐答案
是否表示要更新select语句返回的集合中field1,field2和field3所在的表?
Do you mean that you want to update table where field1, field2, and field3 are in the set returned by your select statement ?
例如
update table,
( select field1, field2, field3
FROM table WHERE field1= 5 AND field_flag =1
GROUP BY field1, field2, field3 limit 1000 ) temp
set table.field_flag = 99
where table.field1=temp.field1 and table.field2=temp.field2 and table.field3 = temp.field3
请注意,此更新可能会更新1000多个行.
Note that the update might update many more than 1000 rows.
也可以使用临时表:
create temporary table temptab as
select field1, field2, field3
FROM table WHERE field1= 5 AND field_flag =1
GROUP BY field1, field2, field3 limit 1000
update table,
temptab temp
set table.field_flag = 99
where table.field1=temp.field1 and table.field2=temp.field2 and table.field3 = temp.field3
这具有以下优点:temptab可以在以后使用,还可以添加索引以加快更新速度:
This has the advantage that temptab can be used later, and also that indexes can be added to speed up the update:
create index on temptab (field1, field2, field3);
这篇关于MYSQL-如何为group by select语句产生的所有项目更新字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文