Avoid error out of range SQL during database migration

Question

i'm stuck on a problem of migration in my SQL Tables,

example i have a table called Error that contains values

latitude DECIMAL(9,2)
longitude DECIMAL(10,8)
info DECIMAL(11,8)
idsf int
sf tinyint
value float

Theses rows have to change to :

latitude DECIMAL(9,2) => DECIMAL(7,1)
longitude DECIMAL(10,8) => DECIMAL(8,6)
info DECIMAL(11,8) => DECIMAL(9,6)
idsf int => DECIMAL(7,2)
sf tinyint => DECIMAL(5,2)
value float => DECIMAL(7,1)

The problem is i have everytime a problem with out of range because some value are not automatically truncated.

Is this possible to have a workaround ?

Thank you for your time

Answer 1

You can update the data to set the values to in-range types. This looks like:

update t
    set latitude = (case when latitude between -999999.9 and 999999.9
                         then latitude
                    end),
        longitude = (case when longitude between -99.999999 and 99.999999
                          then longitude
                     end),
        . . . 

I would recommend, though, that you investigate the values first to determine what the incorrect values look like -- and hence how to handle them.

Even at this point, the data looks suspicious:

• latitude and longitude normally have the same data type.
• longitudes vary from -180 to 180 -- assuming they are measured in degrees.
• latitudes and logitudes would typically have 4 or 6 decimal points, so I would expect something like decimal(9, 6) or something like that.

No doubt, there might be other issues as well.