MySQL: How do i update 50% of the rows, randomly selected?

Question

I want to update 50% of the rows in a table, randomly selected. Is there any way to do that?

Edit: Just to clarify that it should always update 50% of the records, but of those 50% the rows must be randomly selected (not only the top 50% for instance). In other words, in avarage, every other record should be updated.

Answer 1

Should work like that:

UPDATE table SET x = y WHERE RAND() < 0.5

Yep, tested it, works. But of course, it is only 50% of the rows on average, not exactly 50%.

As written in the SQL 92 specification, the WHERE clause must be executed for each tuple, so the rand() must be reevaluated yielding the intended result (instead of either selecting all or no rows at all).

Excerpt from the specification (emphasis mine):

General Rules

1) The <search condition> is applied to each row of T. The result of the <where clause> is a table of those rows of T for which the result of the <search condition> is true.

2) Each <subquery> in the <search condition> is effectively executed for each row of T and the results used in the application of the <search condition> to the given row of T. If any executed <subquery> contains an outer reference to a column of T, then the reference is to the value of that column in the given row of T.

Answer 2

As I said, that's a long way, described in a sort of pseudocode. )

$x = SELECT COUNT(*) FROM some_table;
@ids = SELECT id FROM some_table ORDER BY RAND() LIMIT $x / 2;
UPDATE some_table WHERE id IN (@ids);