UPDATE table using INNER JOIN - help!
I want to update table 'tourneyteamsISF' fields 'pointsfavor' and 'pointscontra' from table 'tourneygamesISF' fiels 'op1score' and 'op2score' from a certain 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 (tourney开发者_如何学运维teamsISF.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
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.
WHERE ID = 1;
<- here is your problem. Add the table name before it and it will compile without any problems.
eg. 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.
it has to be like this - WHERE g1.ID = 1
精彩评论