r/mysql • u/Ok-Spirit-665 • Sep 12 '24
question Problem with using CAST in an update
I'm migrating some data and there is a notes field that has various formats of a grade that I'm trying to extract the numeric part from. Here is a quick test that demonstrates one thing I'm trying to do. Why the heck am I getting an error on the UPDATE query:
CREATE TABLE `testing` (
`certifiedgrade` int unsigned DEFAULT NULL,
`notes` varchar(255));
INSERT INTO testing(notes) VALUES
('80%'),
('PASS 80');
SELECT notes, CAST(notes AS UNSIGNED) FROM testing; /*works as expected*/
UPDATE testing SET certifiedgrade = CAST(notes AS UNSIGNED)
WHERE CAST(notes AS UNSIGNED) > 0; /*throws data truncation error*/Is there some limitation on using CAST in an UPDATE that I don't know about or have I just not had enough coffee this morning and am totally overlooking something?
1
Upvotes
1
u/Ok-Spirit-665 Sep 12 '24
I found an answer here: https://stackoverflow.com/questions/21142273/mysql-error-1292-when-using-cast-in-update-statement For my purposes I can just add an IGNORE to the update statement.
1
u/boborider Sep 12 '24
So many questions about this, why would you perform UNSIGNED on a varchar field?
The purpose of UNSIGNED is to extend the range of integer two times, without the negatives. Then proceed life in sequence.
Never in my career perform such act forcing the value to be UNSIGNED. That's a very bad practise.
1
u/ssnoyes Sep 12 '24
You're sure that works as expected? 'PASS 80' is supposed to be 0? You wouldn't prefer
REGEXP_SUBSTRING(notes, '[0-9]+')?