Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

1690 out of range error

Posted on 2011-09-14
6
Medium Priority
?
444 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 2000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

730 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