2

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?

linuxbuild
  • 15,843
  • 6
  • 60
  • 87
Nir
  • 24,619
  • 25
  • 81
  • 117

2 Answers2

1

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);
Martin
  • 9,674
  • 5
  • 36
  • 36
  • Yes, this is what I wanted. Is it possible to also return the select result so I wont have to query twice? once for the select and once for the flag update. – Nir Feb 23 '10 at 13:27
  • You could perform your select into a temporary table and use that. create temporary table temptab as select field1, field2, ... update table, temptab set table.field = 99 where table.field1 = temptab.field1 and ... – Martin Feb 23 '10 at 13:45
-1

How about:

UPDATE table SET field_flag = <newvalue>
WHERE  idfield IN (
    SELECT idfield
    FROM table
    WHERE <conditions>
)
Brian
  • 6,391
  • 3
  • 33
  • 49