

DROP TABLE IF EXISTS tmp;

CREATE TABLE tmp (
        numero int, 
        codigo char(25),
        compra double,
        custo  double,      
        data   date
        )
;

DROP TABLE IF EXISTS tmp2;

CREATE TABLE tmp2 (
        numero int, 
        codigo char(25),
        compra double,
        custo  double,      
        data   date
        )
;


INSERT INTO tmp
SELECT
     lnh.num_doc,
     lnh.codigo,
     lnh.valor/lnh.quantidade,
     art.ultimo_custo,
     doc.d_doc
FROM documento_linhas_D10 as lnh
LEFT JOIN artigos as art using(codigo)
LEFT JOIN Documento_D10 as doc on (doc.no_doc=lnh.num_doc and doc.serdoc=lnh.serdoc)
WHERE
     length(lnh.codigo)>0 and 
     lnh.valor>0 and  lnh.quantidade>0 
#group by  lnh.codigo
ORDER by lnh.num_doc desc
;

INSERT INTO tmp2
SELECT *
FROM TMP
group by  codigo
;


SELECT *
FROM TMP2
WHERE custo<>compra
;

UPDATE artigos,tmp2
set artigos.ultimo_custo=tmp2.compra
where  tmp2.custo<>tmp2.compra and artigos.codigo=tmp2.codigo
;

