SQL gurus: what is the fastest way to do a select and update

[mihai11]

Hi,



I am selecting a group of rows and I need to update 1 field (set a certain field to 1) for all the rows that meet the search criteria. Can this be done just with SQL (no PHP scripting) ?

I did some research: it looks like I cannot use a sub-query because MySql does not supports this use of sub-queries. For example, my query should look like this:

UPDATE story SET story.story_status=2
WHERE story.thread_id IN (SELECT thread_id FROM story WHERE (condition))

In MySql you cannot use the same table (in this case table "story) for both the subquery's FROM clause and the UPDATE target.



Regards,
Razvan