I am not sure what is this error!
#1292 - Truncated incorrect DOUBLE value:
I don't have double value field or data!
I have wasted a whole hour trying to figure this out!
here is my query
INSERT INTO call_managment_system.contact_numbers
(account_id, contact_number, contact_extension, main_number, created_by)
SELECT
ac.account_id,
REPLACE(REPLACE(REPLACE(REPLACE(ta.phone_number, '-', ''), ' ', ''), ')', ''),'(','') AS Phone,
IFNULL(ta.ext, '') AS extention,
'1' AS MainNumber,
'2' AS created_by
FROM
cvsnumbers AS ta
INNER JOIN accounts AS ac ON ac.company_code = ta.company_code
WHERE
LENGTH(REPLACE(REPLACE(REPLACE(REPLACE(ta.phone_number, '-', ''), ' ', ''), ')', ''),'(','') ) = 10
here is my show create table for the table which the results are going into
CREATE TABLE `contact_numbers` (
`number_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) unsigned NOT NULL DEFAULT '0',
`person_id` int(11) NOT NULL DEFAULT '0',
`contact_number` char(15) NOT NULL,
`contact_extension` char(10) NOT NULL DEFAULT '',
`contact_type` enum('Primary','Direct','Cell','Fax','Home','Reception','Office','TollFree') NOT NULL DEFAULT 'Primary',
`contact_link` enum('Account','PDM','Other') NOT NULL DEFAULT 'Account',
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0 = inactive, 1=active',
`main_number` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 = main phone number',
`created_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_by` int(11) NOT NULL,
`modified_on` datetime DEFAULT NULL,
`modified_by` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`number_id`),
KEY `account_id` (`account_id`),
KEY `person_id` (`person_id`)
) ENGINE=InnoDB AUTO_INCREMENT=534 DEFAULT CHARSET=utf8
You just need to go to the Wamp panel, then to MySQL, then to settings and change the mode to sql-mode: none.
The MySQL error Truncated incorrect DOUBLE value is one of the weirdest errors in MySQL. This is because the error can be caused by some mistakes in your SQL script that has nothing to do with a DOUBLE value. The error is mostly triggered when there's a mistake in UPDATE statements script.
This message means you're trying to compare a number and a string in a WHERE
or ON
clause. In your query, the only potential place where that could be occurring is ON ac.company_code = ta.company_code
; either make sure they have similar declarations, or use an explicit CAST
to convert the number to a string.
If you turn off strict
mode, the error should turn into a warning.
I corrected this error as there was a syntax error or some unwanted characters in the query, but MySQL was not able to catch it. I was using and
in between multiple fields during update, e.g.
update user
set token='lamblala',
accessverion='dummy' and
key='somekey'
where user = 'myself'
The problem in above query can be resolved by replacing and
with comma(,
)
I was facing the same issue. Trying to compare a varchar(100) column with numeric 1. Resulted in the 1292 error. Fixed by adding single quotes around 1 ('1').
Thanks for the explanation above
TL; DR
This might also be caused by applying OR
to string columns / literals.
Full version
I got the same error message for a simple INSERT
statement involving a view:
insert into t1 select * from v1
although all the source and target columns were of type VARCHAR
. After some debugging, I found the root cause; the view contained this fragment:
string_col1 OR '_' OR string_col2 OR '_' OR string_col3
which presumably was the result of an automatic conversion of the following snippet from Oracle:
string_col1 || '_' || string_col2 || '_' || string_col3
(||
is string concatenation in Oracle). The solution was to use
concat(string_col1, '_', string_col2, '_', string_col3)
instead.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With