CODEWITHSUNDEEP
sqlpostgresqlsql-update
JZ.
JZ.

Reputation: 21877

How do I multiply each column by a number then set the new value for each column?

I'm trying to convert lbs to grams. I have an original_amount saved in lbs, and I would like to multiply each of those values by 453.592 to convert to grams.

The query below saves the value of the first select and does not work for each column.

How do I first select the column, then multiply it, then update it?

UPDATE inventory 
SET original_amount = subquery.new_amount 
FROM (SELECT original_amount * 453.592 as new_amount 
      FROM inventory) AS subquery 
where original_units iLike 'lbs';

Upvotes: 0

Views: 65

Answers (1)

JNevill
JNevill

Reputation: 50200

No need for a subquery here, just:

UPDATE inventory 
SET original_amount = original_amount * 453.592     
WHERE original_units iLike 'lbs';

If you want to update the original_units field, you can do that in this query as well:

UPDATE inventory 
SET original_amount = original_amount * 453.592,
    original_units='grams'   
WHERE original_units iLike 'lbs';

Upvotes: 6

Related Questions