out of range value for column

Ancientturtle99 posted 3 months ago in General

So I recently picked up HeidiSQL and am working in a database with thousands of rows of data. I took a class for Oracle SQL, so switching to MySQL is taking a bit of learning. I executed a simple query to update all data in a column: UPDATE item_template SET stat_value1 = stat_value1 * 5 WHERE stat_value1 > 0; I keep getting an error that my data on like "68", and 6000 other lines is out of range value for column. When i go to the column to look at the data inside to see if it's exceeding the value limit set by SMALLINT, and the value is 0. For the life of me, i cannot figure out why it's doing this, or even looking at that row and not just skipping over it.

arucard posted 3 months ago

Not related to HeidiSQL, but error message is pretty clear:

You are trying to update data, that SMALLINT can not handle. SMALLINT has maximum value 32767 (65535 if unsigned). So, try to change to MEDIUMINT or INT, depends on your actual situation.

What is output for this:

SELECT MAX(stat_value1)
FROM item_template;
Ancientturtle99 posted 3 months ago

I ended up abandoning that project and starting on something that is a bit simpler to accomplish and that is just as fun to do. I still would like to figure out why i was getting that error in case i need to use that query in the future. I ran that Query and i got an output of MAX(stat_value1) = 2,500

arucard posted 3 months ago

We are a little bit OT here. So if you want, ping me on kalatalabnik@gmail.com.

Please login to leave a reply, or register at first.