MYSQL - how to update a field for all elements received from a group by select expression

Question

I have a choice:

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 this in MySQL?

Answer 1

You want to say you want to update the table where field1, field2 and field3 are in the set returned by your select clause?

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 

Please note that the update can update more than 1000 lines.

A temporary table may also be used:

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

Answer 2

What about:

 UPDATE table SET field_flag = <newvalue> WHERE idfield IN ( SELECT idfield FROM table WHERE <conditions> )