CODEWITHSUNDEEP
sqlpostgresql
Laurentius
Laurentius

Reputation: 45

Alter Table based on duplicate values in postgresql

I would like to change the type to be the same for the same item. For example my table looks like this:

  item | type
   A   |  1  
   B   |  2  
   A   |  3   
   C   |  4  
   B   |  5   
   A   |  6

Desired output:

  item | type
   A   |  1  
   B   |  2  
   A   |  1   
   C   |  3  
   B   |  2   
   A   |  1

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You can set the value to the smallest:

update t
    set t.type = t2.min_type
    from (select t2.item, min(type) as min_type
          from t t2
          group by t2.item
         ) t2;

If you have a column that specifies the ordering, you can phrase this as:

update t
    set t.type = t2.type
    from (select distinct on (t2.item) t2.item, t2.type
          from t t2
          order by t2.item, t2.?
         ) t2;

SQL tables represent unordered sets. There is no "first" row unless a column specifically contains this information.

Upvotes: 1

Related Questions