当前位置: 动力学知识库 > 问答 > 编程问答 >

php - MySQL - Unknown column '125X' in 'where clause', weird error

问题描述:

I am having a weird MySQL behavior. I am updating mutliple rows at once with queries like this:

UPDATE tableName

SET field1= CASE numbertofind

WHEN 1234 THEN 'voltron'

WHEN 1598 THEN 'optimus Prime'

WHEN 4444 THEN 'redbonzai'

WHEN 125X THEN 'PHP'

END,

field2= CASE numbertofind

WHEN '1234' THEN 'Bozo'

WHEN '1598' THEN 'transformer'

WHEN '4444' THEN 'Teddy Bear'

WHEN '125X' THEN 'Linux'

END

WHERE fieldValue2 IN ('1234', '1598', '4444', '125X');

It all works fine as long as i only have numbers in the WHEN clause. For example, "WHEN 125X" gives me this error:

#1054 - Unknown column '125X' in 'where clauses'

The column exists of course. The "numbertofind" field is of type varchar in my database.

I really have no clue what causes this error, any help is much appreciated!

网友答案:

That's because MySql is handling that 125X as a column name since it is not parsed as a string.

The example for field1 the search for:

WHEN 125X THEN 'PHP'

should be wrapped in quotes as you do for field2:

WHEN '125X' THEN 'PHP'

so the engine knows you are trying to compare the string 125X to the varchar column.

网友答案:

Put this in a single quete as 125x its a string not number ,else mysql will treat it as number or as column name(If it does not look like a number)

WHEN '125X' THEN 'PHP'
网友答案:

You need to put strings in quotes. When a value isn't quoted, it's treated as a number if it looks like a number, otherwise it's treated as a column name (or you get a syntax error if it's not valid syntax for a column name). So your CASE statement for field1 should be like the one for field2, with all the WHEN values being quoted.

网友答案:
WHEN '125X' THEN 'PHP'

Or:

WHEN "125X" THEN 'PHP'

As you did for:

WHEN '125X' THEN 'Linux'

From control flow functions:

The return type of a CASE expression is the compatible aggregated type of all return values, but also depends on the context in which it is used. If used in a string context, the result is returned as a string. If used in a numeric context, the result is returned as a decimal, real, or integer value.

分享给朋友:
您可能感兴趣的文章:
随机阅读: