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:

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:

Not too sure how to go about doing that

CodePudding user response：

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

CodePudding user response：

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;