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

[consolegaming]

Originally Posted by mihai11

This is almost what I need except that the numbers from the "IN" clause must be retrieved from the same table (using another select).

Business logic: I am working on a digg-like clone that integrates well in VB. At this point I need to develop a bot (that will be run on the server at certain intervals) that will promote stories once certain conditions are met. In order to achieve this, my bot would need to scan the stories table then update all the stories that satisfy the given criterias.

I don't want to give more details about how the system is working because that would allow users to cheat my system


Regards,
Razvan

Surely you can just replace thread_id IN (1,2,3) with the conditions you're looking for i.e. if the conditions was that the story had title of 'whatever' and a type of "post" then you'd just put:

UPDATE story SET story_status=2
WHERE title='whatever' AND type='post'

those fields where just examples though as I don't know what conditions you are wanting to be met, if that doesn't sort it I think we'd need to know the conditions you're trying to meet or at least examples of such for us to help any further.