update 25 % of total rows returned by a select query

Question

I have to insert data into a table with columns ( col 1, col2, col 3) .

I have a select query to select the data to be inserted in this table.

Select A.id, b.id , '1' 
from table a 
inner join table b on a.id=b.id
where a.col= 'somevalue'and b.col ='othervalue'  ...

If you notice the third column in the select statment i have a hard coded value '1'. The current requirement is to set only the 25 % of the total rows with '1' and rest 75 % with '0'. Now the difficult part is we cant be sure how many rows the select statement will come up with. We can also use an update statement to update the 25 % rows with 1 and rest keep it as '0' but the limit fucntion will not accept any user defined variable.

Can anyone suggest something on this ?

I am using MYSQL

Answer 1

When you insert the data into the table, keep a row number, and use modulo arithmetic:

insert into . . .
    select a_id, b_id, (mod(rn, 4) = 0) as flag
    from (Select A.id as a_id, b.id as b_id , (@rn := @rn + 1) as rn
          from table a inner join
               table b
               on a.id = b.id cross join
               (select @rn := 0) vars
          where a.col = 'somevalue' and b.col = 'othervalue'  ...
         ) i;

One nice feature of taking every fourth row is that this works well, even if the data is somehow ordered because of the join.