How to change rows in a table based on other table rows in mysql?
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 plea开发者_如何学JAVAse ignore row duplication here.
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
)
$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']");
}
精彩评论