Query to update field from one table based on another

3

Good morning, I need to update the values of one table based on the values that are in another.

I need the first table to update only the data contained in the second, for example, in the first table I have 3,000 items and in the second 330, so I need to update only 330 items.

I think that to do this I have to use SELECT threaded so I put something like this:

SELECT lvi.IdProduto, lvi.VlCustoUnitario
FROM LoteInventarioItem lvi
WHERE lvi.IdProduto IN (SELECT pe.IdProduto FROM Produto_Empresa pe)

This query brings me the 330 items but I need to update the lvi.vlCustoUnitario field with the second table data contained in pe.VlPrecoCusto .

    
asked by anonymous 04.01.2019 / 12:29

1 answer

1
  

I need to update the field lvi.vlCustoUnitario with the data of the second table contained in pe.VlPrecoCusto.

Code sketch, considering that there are no rows repeated by column IdProduto :

-- código #1
UPDATE LVI
  set vlCustoUnitario= PE.VlPrecoCusto
  from Produto_Empresa as PE
       inner join LoteInventarioItem as LVI on LVI.IdProduto = PE.IdProduto
  where ...;
    
04.01.2019 / 12:35