Here I'll assume the rows must be sorted by num, but it would be sorted by another field, like id or timestamp.
The solution in MySQL is to use a inline temporary table to get the num of next row and associate it with current id, and use that table in the UPDATE statement.
Here is the code. Enjoy!
-- the table used for test (MySQL syntax) CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) NOT NULL, `num` int(11) NOT NULL, `diff` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ; -- some values to play on INSERT INTO `test` (`id`, `name`, `num`, `diff`) VALUES (1, 'a', 10, NULL), (2, 'b', 8, NULL), (3, 'a', 18, NULL), (4, 'a', 21, NULL), (5, 'b', 14, NULL), (6, 'a', 32, NULL), (7, 'b', 20, NULL), (8, 'b', 21, NULL); -- a select to test the ability to retrieve the desired diff value select id, name, num, (select min(num) from test where name = t1.name and num > t1.num)-num from test t1 where diff is null order by name, num; -- updating test with the calculated diff using another row on same table. update test t2, ( select id, (select min(num) from test where name = t1.name and num > t1.num)-num as diff from test t1 ) t3 set t2.diff = t3.diff where t2.id = t3.id and t2.diff is null
-- an alternative query to get more than one column from t2
select t1.id, t1.name, t1.num, t2.name, t2.num from test t1, test t2 where t2.id = (select id from test where name = t1.aome and num > t1.num order by num limit 1) order by t1.name, t1.num
See this post in portuguese.