为什么我的SQL查询中出现此错误 - Why am I getting this error in my SQL Query

Why am I getting this error in my SQL Query

本文关键字：错误我的 SQL 查询为什么 | 更新日期: 2023-09-27

尝试此查询时：

 UPDATE l1_a
 SET l1_a.goals_for = l1_a.goals_for + l1_results.score1
 FROM l1_results, l1_a
 WHERE l1_results.team1 = l1_a.team_id;

我得到错误：

   You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM l1_results, l1_a WHERE l1_results.team1 = l1_a.team_id' at line 3

l1_a包含goals_for和team_id

l1_results包含score1（表示进球数）和team1（表示team_id）

我的查询有问题吗？我已经仔细检查了查询中所有列和表的名称是否正确

感谢

documentation for UPDATE声明这是多表UPDATE语句的语法：

Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]

试试这个：

 UPDATE l1_results, l1_a
 SET l1_a.goals_for = l1_a.goals_for + l1_results.score1
 WHERE l1_results.team1 = l1_a.team_id;

您正在组合选择和更新语法。更新语句中没有from。

试试这个：

UPDATE l1_a
SET l1_a.goals_for = l1_a.goals_for + 
  (select l1_results.score1 
  from l1_results 
  where l1_results.team1 = l1a.team_id)

如果l1_results中没有记录，您可能会清空数据。最好添加一个IFNULL：

UPDATE l1_a
SET l1_a.goals_for = l1_a.goals_for + IFNULL(
  (select l1_results.score1 
  from l1_results 
  where l1_results.team1 = l1a.team_id), 0)

更改

UPDATE l1_a
SET l1_a.goals_for = l1_a.goals_for + l1_results.score1
FROM l1_results, l1_a
WHERE l1_results.team1 = l1_a.team_id;

至

UPDATE l1_a
SET l1_a.goals_for = l1_a.goals_for + l1_results.score1
WHERE l1_results.team1 = l1_a.team_id;

您不需要FROM l1_results，l1_a