Improve company productivity with a Business Account.Sign Up

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 451
• Last Modified:

# 1690 out of range error

Hi experts,

My MySQL query is throwing an error if goaldiff is -ve.  It is a signed smallint column.  gfor and gagainst are unsigned tinyint columns.

The error is
1690: BIGINT UNSIGNED value is out of range

UPDATE league_table_firsts
SET played = played + 1, won = won + 0, drawn = drawn + 0, lost = lost + 1,
gfor = gfor + 0, gagainst = gagainst + 4,
goaldiff = CAST(gfor-gagainst AS SIGNED),
points = points + 0
WHERE abbrev = 'Staplehurst Monarchs'

Any help appreciated!

Colin
0
colinspurs
Asked:
• 3
• 3
1 Solution

Commented:
is goaldiff defined as UNSIGNED in your table? IF so, try changing it so it is NOT UNSIGNED
0

Author Commented:
No  - like I said It is a signed smallint column. It takes negative numbers input via workbench.
0

Commented:
try:
``````UPDATE league_table_firsts
SET played = played + 1, won = won + 0, drawn = drawn + 0, lost = lost + 1,
gfor = gfor + 0, gagainst = gagainst + 4,
goaldiff = CAST( CAST(`gfor`-`gagainst` AS UNSIGNED) AS SIGNED),
points = points + 0
WHERE abbrev = 'Staplehurst Monarchs'

Refer to:
http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast

Under Convert > UNSIGNED [INTEGER] section
``````
0

Author Commented:
hielo, thanks but I got the same error.  Thanks for the link - will check it out tomorrow.

Col
0

Commented:
OK, then try casting each column:

``````UPDATE league_table_firsts
SET played = played + 1, won = won + 0, drawn = drawn + 0, lost = lost + 1,
gfor = gfor + 0, gagainst = gagainst + 4,
goaldiff = CAST(gfor as SIGNED) - CAST(gagainst AS SIGNED),
points = points + 0
WHERE abbrev = 'Staplehurst Monarchs'
``````
0

Author Commented:
That's got it!  Thanks.  It had been working for years...wonder why it changed?!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 3
• 3
Tackle projects and never again get stuck behind a technical roadblock.