SQL使用另一个表中的列的算术函数更新新表中的列 [英] SQL Updating column in new table with arithmetic function from column in another table

问题描述

我有两个表:

表1的一部分:

 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

表2:

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

我正在尝试通过获取表1中的那些列并按-((67 * va)/(gp * minutes))来计算prl.我当然可以在将其插入prl列之外进行计算，但是找不到将其插入prl的方法.我最近得到的就是这样做:

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

在整个prl列中给我相同的结果.谁能帮助这个全新的sql用户?

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

推荐答案

使用更新联接:

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;

此处是链接到参考问题的链接，网址为SO涵盖了Postgres中的更新.

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

这篇关于SQL使用另一个表中的列的算术函数更新新表中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！