Solved

1690 out of range error

Posted on 2011-09-14
6
439 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 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