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

[consolegaming]

Wouldn't it just be something like:

UPDATE story SET story_status=2
WHERE thread_id IN (1,2,3)

where 1,2,3 are the various values you're looking for. If not then I think you probably need to explain why you need to use a sub query to be honest.

[mihai11]

Originally Posted by consolegaming

Wouldn't it just be something like:

UPDATE story SET story_status=2
WHERE thread_id IN (1,2,3)

where 1,2,3 are the various values you're looking for. If not then I think you probably need to explain why you need to use a sub query to be honest.

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

[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.

[mihai11]

Originally Posted by consolegaming

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.

The WHERE clause MUST contain a SELECT from the same table. Please see my example from the first post. This is not supported by MySql.

Anyway, I am thinking to do this the old fashion way, with a PHP script. That means, I will read all the required rows then I will apply the processing to each row. I am thinking that with this I will have better error-control.

[consolegaming]

Unless you explain why you need a select you don't need one. The Update does exactly what you're saying with the where conditions in place it find all rows that meet those conditions and then updates them. There isn't any need for a subquery as far as I can see. Everything you've said points at exactly the query I just gave you. You don't require a sub query.

[mihai11]

Originally Posted by consolegaming

Unless you explain why you need a select you don't need one. The Update does exactly what you're saying with the where conditions in place it find all rows that meet those conditions and then updates them. There isn't any need for a subquery as far as I can see. Everything you've said points at exactly the query I just gave you. You don't require a sub query.

This is quite embarrassing. I think that you are right: this can be done without the subquery !

I guess I just put into my mind that the subquery is necessary then I saw no way to do it without a subquery.

[Wayne Luke]

Upgrade to MySQL 5 and create a view for your subquery in the original post. Then it should work properly. Then you don't need to update your code if for instance you want to add additional threads in the future. Without the subquery, you would most likely need a prior query that builds your array to check against the IN clause. Though depending on the where clauses, two queries may be more intensive. Unless you use stored procedures.