使用INNER JOIN的UPDATE表 - 帮助！

I want to update table 'tourneyteamsISF' fields 'pointsfavor' and 'pointscontra' from table 'tourneygamesISF' fiels 'op1score' and 'op2score' from a certain ID.

我想更新表'tourneyteamsISF'字段'pointsfavor'和'pointscontra'来自表'tourneygamesISF'fiels'op1score'和'op2score'来自某个ID。

For example:

例如：

Table name = tourneygamesISF 

op1(name of row) vs. op2    -  op1score = 25 - op2score =  20

I want to update this:

我想更新一下：

op1: (from: tourneygamesISF) = ID in tourneyteamsISF

+ pointsfavor = 25 (to: tourneyteamsISF)
+ pointscontra  = 20 (to: tourneyteamsISF)

op2: (from: tourneygamesISF) = ID in tourneyteamsISF

+ pointsfavor = 20 (to: tourneyteamsISF)
+ pointscontra  = 25 (to: tourneyteamsISF)

I have the following until now

直到现在我还有以下内容

UPDATE tourneyteamsISF 
INNER JOIN tourneygamesISF g1 ON (tourneyteamsISF.ID = g1.op1)
INNER JOIN tourneygamesISF g2 ON (tourneyteamsISF.ID = g2.op2)
SET pointsfavor = pointsfavor 
  + IF(g1.op1score > g1.op2score, g1.op1score - g1.op2score, 0) 
  + IF(g2.op2score > g2.op1score, g2.op2score - g2.op1score, 0)
, pointscontra = pointscontra 
  + IF(g1.op1score < g1.op2score, g1.op2score - g1.op1score, 0) 
  + IF(g2.op2score < g2.op1score, g2.op1score - g2.op2score, 0)
WHERE ID = 1;

But it gives the error: Column 'ID' in where clause is ambiguous

但是它给出了错误：where子句中的列'ID'是不明确的

My tables are like this:

我的表是这样的：

TABLE tourneyteamsISF
  ID integer autoincrement primary key,
  name varchar,
  pointsfavor integer,
  pointscontra integer

TABLE tourneygamesISF
  ID integer autoincrement primary key,
  op1 integer,
  op2 integer,
  op1score integer, /*score for team1*/
  op2score integer /*score for team2*/

I know that it has to do something with naming the id of table teams and games but I am stuck and confused. I will appreciate all the help I can get.

我知道它必须做一些事情来命名表团队和游戏的ID，但我感到困惑和困惑。我将非常感谢能得到的所有帮助。

2 个解决方案

#1

WHERE ID = 1; <- here is your problem. Add the table name before it and it will compile without any problems.

WHERE ID = 1; < - 这是你的问题。在它之前添加表名，它将编译没有任何问题。

eg. WHERE g1.ID = 1;

例如。 WHERE g1.ID = 1;

This error is occuring because SQL cannot identify which ID column you want to use from the tables in your SELECT statment.

发生此错误是因为SQL无法从SELECT语句中的表中标识要使用的ID列。

#2

it has to be like this - WHERE g1.ID = 1

它必须是这样的 - 在哪里g1.ID = 1

#1