Update table from a list of values in postgres

Question

I have a list of 5 values like (30, 41, 49, 28, 50) and I want to update 5 records in a table with those values. Say my table is currently:

id	name	age
1	John	26
2	Pete	39
3	Dave	45
4	Mary	22
5	Jane	42
6	Marv	70

I want to insert those new ages in the table above in the order that I've written them for ids 1 to 5, so the table becomes:

id	name	age
1	John	30
2	Pete	41
3	Dave	49
4	Mary	28
5	Jane	50
6	Marv	70

Not too sure how to go about doing that

This seems to be a very contrived requirement - might one say homework. Please provide a non-SQL based description of the issue. Also post what you have tried and the results. How do these differ from the expected results? If you have not at least attempted a solution they describe the approach you are considering — Belayer
– Belayer, Commented Apr 27, 2022 at 18:30

user330315 · Accepted Answer · 2022-04-27 19:55:42Z

4

You can put the new values into a VALUES clause and use that as the source for an UPDATE statement:

update the_table
   set age = t.new_age
from (
  values (1, 30), (2, 41), (3, 49), (4, 28), (5, 50)
) as t(id, new_age)
where the_table.id = t.id;

answered Apr 27, 2022 at 19:55

user330315

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

Comments

Luuk · Accepted Answer · 2022-04-27 16:31:08Z

1

I do not see your problem:

drop table if exists temp;
create table temp (id INTEGER, name VARCHAR(20), age INTEGER);
insert into temp values
   (1, 'John', 26),
   (2, 'Pete', 39),
   (3, 'Dave', 45),
   (4, 'Mary', 22),
   (5, 'Jane', 42),
   (6, 'Marv',70);
  
select * from temp;

You have a list (30, 41, 49, 28, 50), and a table like above.

Then you can simply insert those new ages one by one:

update temp set age=30 where id=1;
update temp set age=41 where id=2;
update temp set age=49 where id=3;
update temp set age=28 where id=4;
update temp set age=50 where id=5;

select * from temp order by id;

I do not see your problem with this?

Or are you not telling all the constraints?

see: DBFIDDLE

answered Apr 27, 2022 at 16:31

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

2 Comments

Kartashuvit Over a year ago

Yeah i thought of doing that but i have a lot more records to update (a list of 30 values) and want to know if there's a way to avoid writing 30 update set lines

Luuk Over a year ago

What exactly is the problem you are trying to solve? The 5 update statement above can also be written as 1 update statement, that should not make a difference to this "problem".

Collectives™ on Stack Overflow

Update table from a list of values in postgres

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related