Doubt when changing fields automatically subSelect

Question

I would like to know how I do to change all fields of my sales chart.

Modifying the full preco_field that will receive the quantity * preco_unitario.

Remembering that I have 150 records in my sales table, so I would automatically modify from 1 to 150 automatically.

Follow the query currently used.

update
 vendas set preco_total = (select produto.preco_unitario * vendas.quantidade  from vendas inner join produto on vendas.cod_produto = produto.cod_produto
  where vendas.cod_produto = produto.cod_produto) order by cod_venda ;

Answer 1

Carlos, some sgbd implement "calculated column", whose value is usually calculated at run time, that is, when the contents of the column are consulted, using column(s) of the same table. In Postgresql there is how to simulate this functionality, according to the topic "Computed / calculated Columns in Postgresql".

---

The price of a product can vary over time. Suppose an item sold in 2/2/2012, when its price was R$ 2.50 per unit. If in a given transaction 10 units of the item were sold, then the total price of the item sold was R$ 25.00. But if the unit price of this item was readjusted to R $ 3,20 in 3/10/2012, that previous sale will appear as having been $ 32,00. I mean, it was incoherent.

Considering the above in the previous paragraph, I suggest that whenever a sale occurs, the unit price of the item is copied from the product table to the sales table. For this it is necessary to first add the column preco_unitario in the sales table. In this way, in every item sold will always be registered the unit value of the item at the time of sale.

After the creation of the preco_unitario column in the sales table, the update code would be simple:

-- código #1
UPDATE vendas
  set preco_total= preço_unitario * quantidade
  --where cod_venda = ...

---