How to modify multiple rows of a single column in postgresql?

Question

I have following columns and data set to my table. I need to fill the multi column as id*number (i.e. the first value in multi column would be 1*1025=1025, second value would be 2*2587=5174 and so on. I need a postgresql query for this. Do I need a for loop or can be done by some other trick (but I don't want to do it one by one column instead of doing altogether)?

id  multi   number
1           1025
2           2587
3           1475
4           5698
5           254
6           912
7           442
8           8756
9           1123

Then I have got the following query is the simplest way

SELECT 
id, 
number, 
(id * number) as multi
FROM
tableName

This SELECT is working but INSERT or UPDATE is not working with this.

melpomene · Accepted Answer · 2016-12-10 22:07:08Z

2

UPDATE tableName
SET multi = id * number;

Or am I missing something?

answered Dec 10, 2016 at 22:07

melpomene

86.2k8 gold badges97 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LSG Over a year ago

I tried this but I don't know why it is not working, it is giving "Error: more than one row returned by a subquery used as an expression"

LSG Over a year ago

Ok, I have geometry data with column name geom. I want to fill start_geom column from geom column by using ST_startpoint. My query is: UPDATE public.edge_table1 SET (start_geom)= (SELECT ST_StartPoint(ST_Force2D(geom)) FROM public.edge_table1);

melpomene Over a year ago

@LSG UPDATE public.edge_table1 SET start_geom = ST_StartPoint(ST_Force2D(geom));

Collectives™ on Stack Overflow

How to modify multiple rows of a single column in postgresql?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related