Mysql - 'where子句‘中未知的cloumn 'players.p_name’错误代码1054

Question

你好,

首先我要你展示我的桌子：

CREATE TABLE `channels` (
`channel_id` int(11) NOT NULL AUTO_INCREMENT,
`channel_name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`channel_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

CREATE TABLE `gamepoints` (
 `gp_id` int(11) NOT NULL AUTO_INCREMENT,
 `gamble` int(11) DEFAULT NULL,
 `roulette` int(11) DEFAULT NULL,
 `blackjack` int(11) DEFAULT NULL,
 `slots` int(11) DEFAULT NULL,
 PRIMARY KEY (`gp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

CREATE TABLE `player_channel` (
`pc_id` int(11) NOT NULL AUTO_INCREMENT,
`players_id` int(11) DEFAULT NULL,
`channel_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`pc_id`),
 KEY `players_id_idx` (`players_id`),
KEY `channel_id_idx` (`channel_id`),
CONSTRAINT `channel_id` FOREIGN KEY (`channel_id`) REFERENCES `channels` (`channel_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `players_id` FOREIGN KEY (`players_id`) REFERENCES `players` (`players_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

CREATE TABLE `players` (
 `players_id` int(11) NOT NULL AUTO_INCREMENT,
 `p_name` varchar(45) DEFAULT NULL,
 `p_right` varchar(45) DEFAULT NULL,
 `gp_id` int(11) DEFAULT NULL,
PRIMARY KEY (`players_id`),
KEY `gp_id_idx` (`gp_id`),
CONSTRAINT `gp_id` FOREIGN KEY (`gp_id`) REFERENCES `gamepoints` (`gp_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

我想问这个问题：

UPDATE gamepoints SET gamble = 1 
WHERE gamepoints.gp_id = players.gp_id
AND players.p_name = "test"
AND player_channel.players_id = players.players_id
AND player_channel.channel_id = channels.channel_id
AND channels.channel_name = "test";

但是当我这样做的时候，我得到了一个错误：

Error Code: 1054. Unknown column 'players.p_name' in 'where clause'

我不知道我做错了什么，我也尝试了从所有的表中添加，但这也不起作用。

如果有人能帮我，我会很高兴的:)

提前感谢

Answer 1

除了gamepoints之外，您省略了所有表名。我建议您使用显式的联接更新语法，这会使出现这种错误变得更加困难：

UPDATE gamepoints t1
INNER JOIN players t2
    ON t1.gp_id = t2.gp_id
INNER JOIN player_channel t3
    ON t3.players_id = t2.players_id
INNER JOIN channels t4
    ON t4.channel_id = t3.channel_id
SET t1.gamble = 1
WHERE t2.p_name = 'test' AND
      t4.channel_name = 'test';

如果您想挽救当前的查询，那么它将从如下所示开始：

UPDATE gamepoints, players, player_channel, channels
SET gamble = 1
WHERE    -- a very large number of conditions

隐式联接被拒绝的一个原因是，它们将结果集上的正常限制与联接条件混合在一起，所有这些条件都在相同的WHERE子句中。在我使用显式联接编写查询时，这种情况不会发生。