Solved

1690 out of range error

Posted on 2011-09-14
6
435 Views
Last Modified: 2012-05-12
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
Comment
Question by:colinspurs
  • 3
  • 3
6 Comments
 
LVL 82

Expert Comment

by:hielo
ID: 36538617
is goaldiff defined as UNSIGNED in your table? IF so, try changing it so it is NOT UNSIGNED
0
 
LVL 3

Author Comment

by:colinspurs
ID: 36538819
No  - like I said It is a signed smallint column. It takes negative numbers input via workbench.
0
 
LVL 82

Expert Comment

by:hielo
ID: 36539044
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 3

Author Comment

by:colinspurs
ID: 36539683
hielo, thanks but I got the same error.  Thanks for the link - will check it out tomorrow.

Col
0
 
LVL 82

Accepted Solution

by:
hielo earned 500 total points
ID: 36539825
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
 
LVL 3

Author Comment

by:colinspurs
ID: 36541672
That's got it!  Thanks.  It had been working for years...wonder why it changed?!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now