Question 282175 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres updating fields with same value

I have a table as below

name item inum
na1 k1 5
na2 k1 6
na3 k2 7
na4 k3 7
na5 k3 8

If 2 item has the same value, then their inum needs to updated with the highest value
eg,
k1 has 2 rows(na1 and2) with different inum. I need to update inum of na1 with 6 since it is the highest among the 2 k1 inums. Same with k3 as well

name item inum
na1 k1 6
na2 k1 6
na3 k2 7
na4 k3 8
na5 k3 8

Can anyone suggest a postgres code for the same?

Thanks

Tags:

2

Answers


  1. 0

    There are multiple ways to do that and one is to use the lateral join. ie:

    select t1.name, t1.item, x.inum 
  from myTable t1, 
  lateral (select max(inum) from myTable t2 where t1.item=t2.item) x(inum);

    DBfiddle demo

    Another one is to group by and then join. ie:

    select t1.name, t1.item, t2.maxinum 
  from myTable t1
  inner join
  (select item,max(inum) as maxinum from myTable group by item) t2 
     on t1.item=t2.item;

    DBFiddle demo

    Login or Signup to reply.
  2. 0

    You can phrase your query like you phrased your description: tell update that each row has to use the highest inum it can find of the same item. Demo at db<>fiddle:

    update test t                  --tell `update`
set inum=                      --that each row has to use
   (select max(inum) from test --the highest `inum` it can find
    where item=t.item)         --of the same `item`
returning *;                   --(and show what's changed)
    name item inum
    na1 k1 6
    na2 k1 6
    na3 k2 7
    na4 k3 8
    na5 k3 8

    That’s a correlated scalar subquery. The returning clause is there only to show what it did.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search