Solved

1690 out of range error

Posted on 2011-09-14
6
437 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

785 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