Query to update field from one table based on another

Asked

Viewed 86 times

2

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 thousand items and in the second 330, so I need to update only 330 items.

I believe to do this I have to use SELECT chained so I rode 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 field lvi.vlCustoUnitario with the data of the second table contained in pe.VlPrecoCusto.

sql sql-server

  • Could give details of the tables?

  • In the table "Loteinventarioitem" has a unit cost field "Vlcustounitario" which is reset, but I have this data in the table of "Product_company" in the field "Vlprecocusto". These tables have no direct relationship, but the Idproduto field repeats in both, so basically I need to compare the ID’s of the two and where equal, update the data.

  • @Ronaldoalves In some of the tables there is the possibility of having more than one line with the same Idproduto value?

1 answer

1


(...) I need to update the field Lvi.vlCustoUnited with the data of the second table contained in pe.Vlprecocusto.

Sketch of the code considering that there are no lines repeated by the 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 ...;

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.