mysql - 使用if语句更新sql表

Question

我正在尝试使用基于我存储在另一个表中的数据的“if”语句来更新名为 user_results 的表。一些背景知识:用户正在进行每周的选择(游戏 1、游戏 2 等)，我想将选择与结果进行匹配，以查看是否应该奖励积分。如果他们选择正确，他们将获得下注的积分(points1、point2 等)，并且所有这些都将在 user_results 表的 week1 列中更新。

以下是 week1_picks 表的示例:

username     game1             points1     game2             points2
jake25    New England Patriots   4      Green Bay Packers     5
test24    Pittsburgh Steelers    1      Chicago Bears         1

nfl_schedule 表如下所示:

 game  away                    home                      winner
 1   Pittsburgh Steelers  New England Patriots   New England Patriots
 2   Green Bay Packers      Chicago Bears        Green Bay Packers

为了尝试测试其中的一部分以使某些功能正常工作，我尝试了以下操作:

update user_results
    if user_week1_picks.game1 = New England Patriots then
        set week1 = points1 
        where username = user_results.username; 
else 
    set week1 = 0
    where username = user_results.username;

我不断收到错误消息:#1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 'if user_week1_picks.game1 = New England Patriots then 附近使用的正确语法 设置周 1 = 点 1' 在第 2 行

我想要的结果是，玩家的所有每周得分都是通过此查询计算的，因此它看起来像这样:

username week1 week2
jake25     9     0
test24     0     0

Answer 1

里面有问题吗？或者您是否提供了状态报告？

根据 MySQL 引用手册，MySQL 返回该特定错误是因为 SQL 文本的语法(实际上)无效。

引用:MySQL Reference Manual 13.2.11 UPDATE Syntax http://dev.mysql.com/doc/refman/5.6/en/update.html

<小时/>

要执行您似乎描述的操作，您可以使用如下语法:

  UPDATE user_results r
     SET r.week1 = expr
   WHERE r.username = 'yosemite sam'

表达式expr可以使用(有效的)MySQL语法，包括IF()函数，或者更符合ANSI标准的CASE 表达。

但看起来您想引用其他表中的列值。为此，您可能可以使用子查询，但看起来更像是您想要使用 UPDATE 语句的多表形式。

最简单的方法是编写一个返回要更新的列的 SELECT 语句，以及一个返回要分配的新值的表达式。

要获取 game1 结果，如下所示:

  SELECT r.username
       , r.week1
       , IF(s1.winner=p1.game1, p1.points1, 0) AS week1_newval
    FROM user_results r
    LEFT JOIN nfl_schedule s1  ON s1.game = 1 
    LEFT JOIN week1_picks  p1  ON p1.username = r.username

你还想获得game2、game3等结果。 SQL 将会更加复杂，主要是因为 week1_picks 表没有为每个游戏 (1, 2) 提供单独的行...而您想要获得总计。所以我们必须用更复杂的 SQL 来适应这一点。我们可以通过几种方法来做到这一点。但我们现在将跳过该讨论。

为了将该 SELECT 语句转换为多表 UPDATE，我们将 SELECT ... FROM 替换为 UPDATE 关键字，并添加 SET 子句将表达式的结果分配给我们要更新的列。

例如:

  UPDATE user_results r
    LEFT JOIN nfl_schedule s1  ON s1.game = 1 
    LEFT JOIN week1_picks  p1  ON p1.username = r.username
     SET r.week1 = IF(s1.winner=p1.game1, p1.points1, 0)

这只是我们如何将 SELECT 语句转换为 UPDATE 语句的示例。首先将其编写为 SELECT 语句，让我们返回要更新的行、当前值以及要分配的新值。这让我们可以看到发生了什么，并帮助我们开发查询条件和表达式以获得我们想要的结果。

<小时/>

您需要的 SELECT 语句将比这个简单的示例更加复杂，因为您想要检查多个游戏。要获得总分，我们可以使用 GROUP BY 子句和 SUM() 聚合，并且我们可能希望在内联 View 中执行此操作...大致如下:

  SELECT r.username
    FROM user_results r
    JOIN ( SELECT p.username
                , s.game
                , r.week1
                , SUM( 
                    CASE s.game
                    WHEN  1 THEN IF(p.game1=s.winner,p.points1,0) 
                    WHEN  2 THEN IF(p.game2=s.winner,p.points2,0)
                    WHEN  3 THEN IF(p.game3=s.winner,p.points3,0)
                    WHEN  4 THEN IF(p.game4=s.winner,p.points4,0)
                    ELSE 0 
                    END
                  ) AS total_earned_points 
             FROM week1_picks p 
             LEFT
             JOIN nfl_schedule s
           GROUP BY p.username
         ) q
      ON q.username = r.username 
   ORDER BY r.username