Update using values contained in another table

Question

How can I update a table by setting the value of a field with the same value contained in another table for all records?

I have a 'product' table and a 'movproduto' table. I want to update the information regarding the cost price in the 'product' table with the same value contained in the table 'movproduto'.

tabela 'produto'
codproduto
vlrprecocusto

tabela 'movproduto'
codproduto
vlrprecocusto

I tried a few ways and I didn’t succeed. Example of how I was trying:

update produto p 
inner join movproduto m on m.codproduto = p.codproduto
    set p.vlrprecocusto = m.vlrprecocusto;

This sql returns the following error:

token unknown - line 2, column 5
inner

I’m using Firebird 2.5

Answer 1

Guy unfortunately FIREBIRD does not accept update with Inners...

You would have to do something +/- like this

update produto set vlrprecocusto = valor;
  where codproduto in (select codproduto 
                         from produto
                        inner join movproduto m on m.codproduto = p.codproduto)

Note: Check my update before running it to not damage your data in case I have put some wrong information.

Edited

I’m sorry, in the example above I just showed the structure without paying attention to the fields, it would serve for you to set a fixed value for the codproduct field...

In case to do the update you want would have to do so

EXECUTE BLOCK AS
  DECLARE VARIABLE iCodProduto INTEGER;
  DECLARE VARIABLE nValorCusto NUMERIC(18,4);
BEGIN
  FOR select p.codproduto 
            ,m.vlrprecocusto
        from      produto    p
       inner join movproduto m on m.codproduto = p.codproduto INTO :iCodProduto, :nValorCusto DO
  BEGIN
    update produto set vlrprecocusto = :nValorCusto where codproduto = :iCodProduto;
  END
END;

If you’re running this script on some project you’re doing, see if the component you use accepts execute block, for example, the Tibquery of Delphi 7 would not run this script, but if it is running directly in the database, then it runs smoothly.

Answer 2

Try the following:

update produto p
set
  p.vlrprecocusto = (select 
                       m.vlrprecocusto
                     from movproduto m 
                     where 
                       (m.codproduto = p.codproduto))

Explanation

For each record in the "product" table the same code record will be selected in the "movproduto" table; The value of the "vlrprecocusto" field of the "movproduto" table will be recorded in the "vlrprecocusto" field of the "product" table".

Heed

• If no record with the same code is located in the table "movproduto" will be written NULL in the "product"; If you want to avoid NULL you can use the function COALESCE, but if you want "product" without "movproduto" not to be changed, then you should-include in the WHERE clause a condition to affect only records that exist in the "movproduto".

• If there are duplicated records with the same "codproduct" in the "movproduto" table the error "Multiple Rows in Singleton select" will occur. To solve this you can use "FIRST 1" in the sub-select or use GROUP BY with the AVG function in the "vlrprecocusto" field of the table "movproduto".

Any questions, ask me.