Usando dados de outra linha no UPDATE
2012-05-28 19:38:16
2012-05-22 19:43:49
Autor: Sony Santos
https://gigawiki.com/sony/usando-dados-de-outra-linha-no-update
Permalink: https://gigawiki.com/2895
área = informática; assunto = mysql, sql; categ = dicas, list, soluções
O problema: eu tenho uma tabela com nomenum e dif. O que é atípico aqui é que dif deve ser atualizado com a diferença entre dois nums consecutivos para o mesmo nome. Então eu preciso conhecer o num da próxima linha (para o mesmo nome) para atualizar a atual. E eu quero fazer isso em SQL com um único UPDATE.

Aqui eu estou assumindo que as linhas devam ser ordenadas pelo num, mas poderia ser por outro campo, como id ou timestamp.

A solução em MySQL é usar uma tabela temporária para pegar o num da próxima linha e associá-la com o id da atual, e usar essa tabela no UPDATE.

Segue o código. Divirta-se! 

-- a tabela usada para teste (sintaxe MySQL)
CREATE TABLE IF NOT EXISTS `teste` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(30) NOT NULL,
  `num` int(11) NOT NULL,
  `dif` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

-- alguns valores para brincar
INSERT INTO `teste` (`id`, `nome`, `num`, `dif`) 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);

-- uma query para testar a capacidade de pegar o dif desejado
select id, nome, num, (select min(num) from teste where nome = t1.nome and num > t1.num)-num
from teste t1
where dif is null
order by nome, num;

-- atualizando a tabela com o dif calculado com o num de outra linha
update teste t2, (
  select id, (select min(num) from teste where nome = t1.nome and num > t1.num)-num as dif
  from teste t1
) t3
set t2.dif = t3.dif
where t2.id = t3.id
and t2.dif is null

-- uma query alternativa para tornar possível obter mais de uma coluna (em t2)
select t1.id, t1.nome, t1.num, t2.nome, t2.num
from teste t1, teste t2
where  t2.id = (select id from teste where nome = t1.nome and num > t1.num order by num limit 1)
order by t1.nome, t1.num

See this post in English.
blog comments powered by Disqus