Update PostgresSQL column with computed data from the same table

Question

I need to update values in a PostgreSQL table based on information from the same table.

For example the table look like this

Before Update:

index	shop_id	tire_type	count
0	0	Winter	Null
1	0	Summer	Null
2	0	Winter	Null
3	0	Winter	Null
4	1	Summer	Null
5	1	Winter	Null

After Update:

index	shop_id	tire_type	count
0	0	Winter	3
1	0	Summer	1
2	0	Winter	3
3	0	Winter	3
4	1	Summer	1
5	1	Winter	1

For this example the table contains types of tires connected the the shop table via shop_id. The count column should contain the number of the same tire types on the same shop_id.

From example above we can see that the number of the type Winter at shop_id = 0 is 3 so each row with Winter type should have the number 3 in count column.

How can this be done with SQL ? Or with Python Pandas DataFrame on an existent DataFrame (that will be inserted on an existent PostgresSQL table) ?

P.S. The table schema can't be changed. The preferred solution is with Pandas but it is ok with PostgreSQL.

Mayank Porwal · Accepted Answer · 2021-04-26 13:20:39Z

1

Use Groupby.transform:

In [30]: df['count'] = df.groupby(['shop_id', 'tire_type'])['count'].transform('count')

In [31]: df
Out[31]: 
   index  shop_id tire_type  count
0      0        0    Winter      3
1      1        0    Summer      1
2      2        0    Winter      3
3      3        0    Winter      3
4      4        1    Summer      1
5      5        1    Winter      1

Then write this df back to Postgres using df.to_sql.

answered Apr 26, 2021 at 13:20

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2021-04-26 13:19:42Z

1

You can use window functions:

select t.*,
       count(*) over (partition by tire_type, shop_id)
from t;

If you need to update the value, then you can use aggregation in an update statement:

update t
    set count = tt.cnt
    from (select tire_type, shop_id, count(*) as cnt
          from t
          group by tire_type, shop_id
         ) tt
    where t.tire_type = tt.tire_type and t.shop_id = tt.shop_id

answered Apr 26, 2021 at 13:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Update PostgresSQL column with computed data from the same table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related