mysql update group_MYSQL-如何为group by select语句产生的所有项目更新字段

I have a select like this:

SELECT field1, field2, field3

FROM table WHERE field1= 5 AND field_flag =1

GROUP BY field1, field2, field3 limit 1000;

I want to update field_flag for the resulting rows. How can I do that in MySQL?

解决方案

Do you mean that you want to update table where field1, field2, and field3 are in the set returned by your select statement ?

eg.

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

Note that the update might update many more than 1000 rows.

A temporary table could be used too:

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

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);