How to UPDATE a table using data from another row

The Problem: I have a table with name, num, and diff. The unusual case here is that the diff must be updated with the difference between two sibling nums for the same name. So I'll need to know the num of the next row (for the same name) to update the current one. AND I want to do that in SQL with a single UPDATE.

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.