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:
colinspurs
  • 3
  • 3
1 Solution
 
hieloCommented:
is goaldiff defined as UNSIGNED in your table? IF so, try changing it so it is NOT UNSIGNED
0
 
colinspursAuthor Commented:
No  - like I said It is a signed smallint column. It takes negative numbers input via workbench.
0
 
hieloCommented:
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

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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

Col
0
 
hieloCommented:
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'

Open in new window

0
 
colinspursAuthor 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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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