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
10 Answers
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.
I've seen a couple cases where this error occurs:
1. using the not equals operator != in a where clause with a list of multiple or values
such as:
where columnName !=('A'||'B')
This can be resolved by using
where columnName not in ('A','B')
