SQL Updating column in new table with arithmetic function from column in another table

Question

I have two tables:

part of Table1:

 id  |  per  |  va   | gp | minutes 
-----+-------+-------+----+---------
   1 | 11.87 |  14.5 | 69 |    16.1
   2 | 17.07 | 154.9 | 78 |    23.9
   3 |  4.30 |  -8.6 | 21 |     4.4
   4 |  5.68 | -42.2 | 52 |     9.3
   5 | 19.46 | 347.8 | 82 |    32.1
   6 | 18.26 | 125.3 | 47 |    23.3
   7 | 12.76 |  79.0 | 82 |    28.5
   8 |  9.19 |  -3.7 | 13 |    14.8
   9 | 21.15 |  10.7 | 10 |     6.8
  10 | 14.38 |  46.1 | 31 |    25.7

Table2:

 player |           prl           | position 
--------+-------------------------+----------
 1      |                         | 
 2      |                         | 
 3      |                         | 
 4      |                         |

I'm trying to calculate prl by taking those columns in table 1 and doing per - ((67*va)/(gp*minutes)). I can of course calculate this outside of inserting it into the prl column but can't find a way to get it into prl. The closest I've gotten is by doing this:

update hwk2
set prl = per - ((67*va)/(gp*minutes)) from more_player_stats;
UPDATE 1904

which gives me the same result throughout the entire prl column. Can anyone help out this brand new sql user?

Can you tell us how these two tables are logically connected? — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 18, 2017 at 6:47
The first table is a bunch of NBA stats from players, but it doesn't list the players' position. So there's this stat an NBA analyst created that is the 'prl', and its formula is what I described. Basically the prl should take about 4 values (11.5 for power forwards, 11 for point guards, 10.6 for centers, etc.) So I am trying to get the players' position from the columns in Table 1 if that helps and calculate each players' 'prl' based on these stats. — intern14
– intern14, Commented Nov 18, 2017 at 6:53
Let me rephrase my question. How do we connect players in the second table to stats in the first table? — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 18, 2017 at 6:58
Oh, that's already done. I inserted the players column already as just an id number - I'm just trying to get just the prl column to equal that formula in the original post. — intern14
– intern14, Commented Nov 18, 2017 at 7:01
We're going in circles. We already know that the second table has numbered players. But what is not clear is how to connect those players to the first table. — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 18, 2017 at 7:03

Tim Biegeleisen · Accepted Answer · 2017-11-18 07:28:39Z

1

Use an update join:

UPDATE hwk2 AS t1
SET prl = t1.per - ((67*t1.va) / (t1.gp*t1.minutes))
FROM more_player_stats AS t2
WHERE t1.player = t2.id;

Here is a link to a reference question on SO covering updates in Postgres.

edited Nov 18, 2017 at 7:28

answered Nov 18, 2017 at 7:08

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

5 Comments

intern14 Over a year ago

Thank you so much! I've tried this and keep getting a HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Tim Biegeleisen Over a year ago

I was missing an alias in there. Not sure about casting; if all columns invovled are numeric there should be no issue.

intern14 Over a year ago

Thanks so much - this is very helpful. I'll play around with it. Probably something weird in table I did. Appreciate it!

intern14 Over a year ago

I just got it! I had to do where t1.player::numeric = t2.id::numeric; Not sure why, but I got it. Thanks so much!

Tim Biegeleisen Over a year ago

This is strange, but glad you figured it out.

Collectives™ on Stack Overflow

SQL Updating column in new table with arithmetic function from column in another table

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related