Doubt when changing fields automatically subSelect

Question

Doubt when changing fields automatically subSelect

Navigation

#1 by (0 votes)

0

I would like to know how I can change all the fields in my sales table.

By modifying the field total_coin that will receive the quantity * preco_unitario.

Remembering that I have 150 records in my sales table, then I would like to automatically change 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 ;

sql postgresql

asked by anonymous 03.12.2016 / 02:09

1 answer

Know the type of return of a given Spinner with a call icon

score 0 · Answer 1

Carlos, a few sgbd implement "calculated column" whose value is usually calculated in time, ie when the contents of the column are queried, using column (s) of the same table. In PostgreSQL there is a way to simulate this functionality, according to the topic " Computed / calculated columns in PostgreSQL ".

The price of a product can vary over time. Assume an item sold on 2/2/2012, when the price was R $ 2.50 per unit. If in a given transaction were sold 10 units of the item, then the total price of the item sold was $ 25.00. But if the unit price of this item was readjusted to R $ 3.20 on October 3, 2012, that previous sale will be recorded as having been R $ 32.00. That is, it was inconsistent.

Considering 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 add the preco_unitario column 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 creating 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 = ...