0

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?

intern14
  • 59
  • 2
  • 8
  • Can you tell us how these two tables are logically connected? – Tim Biegeleisen Nov 18 '17 at 06: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 Nov 18 '17 at 06:53
  • Let me rephrase my question. How do we connect players in the second table to stats in the first table? – Tim Biegeleisen Nov 18 '17 at 06: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 Nov 18 '17 at 07: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 Nov 18 '17 at 07:03
  • My apologies!! I just edited my post - the id number in Table 1 is = player # in Table 2 – intern14 Nov 18 '17 at 07:06

1 Answers1

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 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. – intern14 Nov 18 '17 at 07:26
  • I was missing an alias in there. Not sure about casting; if all columns invovled are numeric there should be no issue. – Tim Biegeleisen Nov 18 '17 at 07:30
  • Thanks so much - this is very helpful. I'll play around with it. Probably something weird in table I did. Appreciate it! – intern14 Nov 18 '17 at 07:32
  • 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! – intern14 Nov 18 '17 at 07:34
  • This is strange, but glad you figured it out. – Tim Biegeleisen Nov 18 '17 at 07:35