如何解决 MySQL 错误代码：1292。截断了不正确的 DOUBLE 值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15840843/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to resolve MySQL Error Code: 1292. Truncated incorrect DOUBLE value?
提问by ben
I'm trying to update 2 different tables with an update query as shown below
我正在尝试使用更新查询更新 2 个不同的表，如下所示
UPDATE db1.table1 a, db2.table1 b
SET b.firstname = a.firstname,
b.lastname = a.lastname,
b.address = a.address,
b.state = a.state,
b.city = a.city,
b.zip = a.zip
WHERE a.stud_id=b.stud_id AND a.firstname IS NOT NULL AND b.firstname IS NULL
AND str_to_date(a.joindate,'%m/%d/%Y') >= str_to_date('02/01/2012','%m/%d/%Y');
but when i tried to execute this query, MySQL kept throwing the following error
但是当我尝试执行此查询时，MySQL 不断抛出以下错误
Error Code: 1292. Truncated incorrect DOUBLE value: 'CROUGH0000'
Though i've found much similar posts in stackoverflow, i couldn't find the exact solution to this problem.
虽然我在 stackoverflow 中发现了很多类似的帖子，但我找不到这个问题的确切解决方案。
Need some help. Thanks in advance
需要一些帮助。提前致谢
EDIT : Datatypes of each column are as follows
编辑：每列的数据类型如下
b.firstname(varchar(25)) = a.firstname(varchar(52)),
b.lastname(varchar(25)) = a.lastname(varchar(35)),
b.address(varchar(40)) = a.address(varchar(50)),
b.state(char(2)) = a.state(char(2)),
b.city(varchar(25)) = a.city(varchar(25)),
b.zip(varchar(11)) = a.zip(varchar(11))
回答by R. Dwivedi
For me this error was showing because I was using 'and' to update 2 Queries instead of a 'comma'.
对我来说，显示此错误是因为我使用“和”来更新 2 个查询而不是“逗号”。
This snippet shows the error:
此代码段显示错误：
UPDATE employees
SET
lastname = 'Hill'and
email = '[email protected]'
WHERE
employeeNumber = 1056;
This snippet shows the correction:
此代码段显示了更正：
UPDATE employees
SET
lastname = 'Hill',
email = '[email protected]'
WHERE
employeeNumber = 1056;
回答by schlebe
I think that joindatecolumn contains a String value that is not String-Date.
我认为该joindate列包含一个不是 String-Date 的 String 值。
To check that, if possible, I propose to test following SQL command
为了检查，如果可能的话，我建议测试以下 SQL 命令
UPDATE db1.table1 a
,db2.table1 b
SET b.firstname = b.firstname,
WHERE a.stud_id=b.stud_id
AND a.firstname IS NOT NULL
AND b.firstname IS NULL;
If this work, nothing has been changed but you know that your issue is linked to joindatefield or str_to_datefunction.
如果这项工作有效，则没有任何更改，但您知道您的问题与joindate字段或str_to_date功能有关。
Caution: b.firstname is assigned to same value -> nothing is changed
注意：b.firstname 被分配给相同的值 -> 没有任何改变
回答by D Mac
If all those columns are varchar (as you've stated above) then the problem must be with a.stud_id=b.stud_id.
如果所有这些列都是 varchar（如上所述），那么问题一定出在a.stud_id=b.stud_id.
Check your data types in both tables a and b. Something has to be a DOUBLE or MySQL wouldn't be complaining about it.
检查表 a 和 b 中的数据类型。某些东西必须是 DOUBLE 或 MySQL 不会抱怨它。
