Unknown Column Error on Update

Question

I am experiencing some strange behavior on one of my tables that I cannot track down. Anytime I try to perform an update I get an Unknown Column Error

When I try to run:

UPDATE `bakerevents` SET `score`=300 WHERE `id`=2910;

I receive the following error:

Unknown column 'bakerevents.id' in 'field list'

When I run a select on this id I receive no errors and the row is returned:

SELECT * FROM `bakerevents` WHERE `id`=2910;
{
    "data":
    [
        {
            "id": 2910,
            "match_id": 1404,
            "game_number": 2,
            "school_id": 3,
            "score": 162,
            "result": 1,
            "updated_at": "2014-12-20 05:31:13",
            "create_user_id": 24,
            "update_user_id": 0,
            "created_at": "0000-00-00 00:00:00"
        }
    ]
}

See below for my database create table syntax:

CREATE TABLE `bakerevents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `match_id` int(11) NOT NULL,
  `game_number` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  `score` int(11) NOT NULL DEFAULT '0',
  `result` decimal(11,1) NOT NULL COMMENT '1=Win 0=Loss',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_user_id` int(11) NOT NULL DEFAULT '0',
  `update_user_id` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7251 DEFAULT CHARSET=latin1;

Any thoughts on what is causing the error?

Also, I have tried a delete statement and that appears to work fine. I seem to be only having the problem on updates. Other similar tables the update works correctly, it's just this table. — josh088, Apr 07 '16 at 01:39

score 1 · Accepted Answer · answered Apr 07 '16 at 11:33

I was able to finally track this down this morning. I had an old post update trigger on this table that was causing the issue. The trigger was not successfully completing and that was causing the problem. Once i removed the trigger the update completed without error. If you experience this in the future, be sure to check your triggers.

score 0 · Answer 2 · answered Apr 07 '16 at 01:27

0

Your error is strange, because it includes both the table name and the column name. I would expect that error for this version:

UPDATE `bakerevents`
    SET `score` = 300
    WHERE `bakerevents.id` = 2910;

None of your names need escaping. So try without the backticks:

UPDATE bakerevents
    SET score = 300
    WHERE id = 2910;

answered Apr 07 '16 at 01:27

Gordon Linoff

1,242,037
58
646
786

Same error message when trying your suggestion. I've tried Sequel Pro, MYSQL Workbench and a terminal and all result in the same error. – josh088 Apr 07 '16 at 01:33

Unknown Column Error on Update

2 Answers2