I'm getting an odd 'Truncated incorrect INTEGER value' error when I run the following UPDATE query:
update tbl
set projectNumber = right(comments, 7)
where createdBy = 'me'
and length(CONVERT(right(comments, 7), SIGNED INTEGER)) = 7
and CONVERT(right(comments, 7), SIGNED INTEGER) > 0
and CONVERT(right(comments, 7), SIGNED INTEGER) is not null
and createdOn > '2011-01-31 12:00:00'
and projectNumber is null
projectNumber is varchar(10).
When I run it as a straight select I do not get an error and I see results as expected. Any ideas? Essentially I'm trying to update the projectNumber field where the end of the comments in imported notes are 7 numeric characters (but projectNumber's are not always 7 numeric, which is why the field is varchar(10)).
It's not an error. It's a warning that comes from CONVERT() when you ask it to convert non-numeric to integer;
Run these queries in console to see:
mysql> SELECT CONVERT(right('1s23d45678', 7), SIGNED INTEGER);
+-------------------------------------------------+
| CONVERT(right('1s23d45678', 7), SIGNED INTEGER) |
+-------------------------------------------------+
| 3 |
+-------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '3d45678' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)
As I said, it's a warning, not an error. Your query should be doing the update correctly.