How to change rows in a table based on other table rows in mysql?

Question

I've a table which has 3 columns: id, a_id and b_id.

Suppose rows are like this:

1, a1, b1
2, a1, b2
3, a1, b3
4, a2, b4
5, a2, b5
6, a2, b6

I want to convert it to

1, a1, b1
2, a1, b1
3, a1, b1
4, a2, b4
5, a2, b4
6, a2, b4

So I want to make all the b_id corresponding to a_id same, and equal to the one which is found first. How can I do this? For simplicity, I've removed other columns from table. So please ignore row duplication here.

Answer 1

$result = mysql_query("SELECT a_id, b_id
FROM table
GROUP BY a_id
ORDER BY id ASC");

while ($row = mysql_fetch($result))
{
    mysql_query("UPDATE table SET b_id=$row['b_id'] WHERE a_id=$row['a_id']");
}

Answer 2

Assuming b_id is the mathematically smallest in each id group:

UPDATE
  tbl
SET
  b_id = (
    SELECT MIN(b_id) 
      FROM tbl AS i
     WHERE i.id = tbl.id AND i.a_id = tbl.a_id
  )