Mysql - Error code 1054 Unknown cloumn 'players.p_name' in 'where clause'

Hello,

first of all I want you to show my table I have:

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

I wanted to this query:

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";

But when I do this i get this Error:

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

I don't know what I am doing wrong, i tryed also after set to add from with all tables, but this works also not.

I would be really happy if someone can help me :)

Thanks in advance

Solution

You omitted the table names of all but gamepoints. I would recommend that you use explicit join update syntax, which makes it harder to have this sort of error:

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';

If you wanted to salvage your current query, then it would start off looking something like this:

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

One reason implicit joins are frowned upon is that they mix normal restrictions on the result set with join conditions, all in the same WHERE clause. This doesn't happen with the query as I wrote it using explicit joins.