Mysql update the selected rows using single query

Question

I have to select 3 rows from a table book(id,book_id,status) with status status=0.Initially all rows have status 0.So by using a select query.order by id and limit 3 I can select first 3 rows.The question is how can I update the selected rows status to 1 since they are selected.

Am using PHP, CodeIgniter and MySQL, can I do it in a single query?

Example:

id  book_id  status 
1     100      0
2     101      0
3     102      0
4     103      0
5     104      0
6     105      0

After selecting the first 3 rows the that rows's status should be updated to 1

id  book_id  status 
1     100      1
2     101      1
3     102      1
4     103      0
5     104      0
6     105      0

Answer 1

sqlfiddle demo

UPDATE t JOIN (select id from t where status = 0 
                    order by id limit 3) t1 USING (id)
SET status=1 ;

Answer 2

I have used MySQL's own RAND(N) function for this issue.

The reference says that:

If an integer argument N is specified, it is used as the seed value:

One implication of this behavior is that for equal argument values, RAND(N) returns the same value each time, and thus produces a repeatable sequence of column values.

So I can select a random set of records from the tables, and using the same seed, I can update exactly the same records without going through any of it.

And get something like this:

SELECT id, book_id 
FROM book 
WHERE status = 0 
ORDER BY RAND({your_seed}) 
LIMIT 0, 3;

UPDATE book 
JOIN ( 
  SELECT id 
  FROM book 
  WHERE status = 0 
  ORDER BY RAND({same_seed}) 
  LIMIT 0, 3 
) temp USING (id) 
SET status = 1;

This way you get your selection and also updated the exact same list.