Re: Format an Update with calculation

Bret,

That's just an example of how to use Postgres' cast syntax.  You'd write:
UPDATE im_ci_item_transfer
    SET suggested_retail_price=(suggested_retail_price + 
(suggested_retail_price * .13))::numeric(7,2)
WHERE item_code='0025881P2';

I prefer the round() function, though.


On 12/18/2018 09:51 AM, Bret Stern wrote:
> Thanks again,
> I don't remember ever using a select in an update.
> Not sure how to use a select in an update, I'll google around.
>
>
> On Tue, 2018-12-18 at 08:18 +0100, Pavel Stehule wrote:
>>
>>
>> út 18. 12. 2018 v 8:15 odesílatel Bret Stern
>> <bret_stern@machinemanagement.com> napsal:
>>
>>          My statement below updates the pricing no problem, but I want
>>          it to be
>>          formatted with 2 dec points eg (43.23).
>>          
>>          Started playing with to_numeric but can't figure it out. Lots
>>          of examples with to_char in the
>>          manual, but still searching for answer.
>>          
>>          Can it be done?
>>          
>>
>>
>> postgres=# select random();
>> ┌───────────────────┐
>> │      random       │
>> ╞═══════════════════╡
>> │ 0.261391982901841 │
>> └───────────────────┘
>> (1 row)
>>
>> postgres=# select random()::numeric(7,2);
>> ┌────────┐
>> │ random │
>> ╞════════╡
>> │   0.67 │
>> └────────┘
>> (1 row)
>>
>>
>>   
>> Regards
>>
>>
>> Pavel
>>
>>
>>          
>>          I want suggested_retail_price to be formatted to 2 decimal
>>          points
>>          
>>          UPDATE im_ci_item_transfer
>>             SET suggested_retail_price=(suggested_retail_price +
>>          (suggested_retail_price * .13))
>>          WHERE item_code='0025881P2';
>>          
>>          Feeling lazy, sorry guys
>
>

-- 
Angular momentum makes the world go 'round.