Hello List,
So sorry about the bad click. I meant to "Reply to list" but instead
just replied to the original poster.
This is the exact same advice that hsv@ just provided. If I had paid
attention I could have saved him the duplication of efforts. My
apologies to him and everyone else.
Humbly embarrassed,
Shawn
-------- Forwarded Message --------
Subject: Re: Something strange here...
Date: Wed, 14 Jun 2017 14:04:02 -0400
From: shawn l.green <shawn.l.gr...@oracle.com>
Organization: Oracle Corporation
To: Chris Knipe <sav...@savage.za.org>
Hello Chris,
On 6/13/2017 5:42 PM, Chris Knipe wrote:
Hi all,
Can someone explain to me why this is happening please:
mysql> SELECT * FROM CustomerDetails WHERE Username=’blah’\G
*************************** 1. row ***************************
EntryID: F83D4109-DCA8-426F-98B1-00B4AF117ACB
Username: blah
AccountVolume: 0
1 row in set (0.00 sec)
mysql> UPDATE CustomerDetails SET
AccountVolume=GREATEST(CAST(AccountVolume-2865 AS SIGNED), CAST(0 AS SIGNED))
WHERE Username='blah';
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in
'(`test`.`CustomerDetails`.`AccountVolume` - 2865)'
Using GREATEST, shouldn’t it be irrelevant whether AccountVolume-INT is signed
or unsigned?
How would I go about doing this? I have played quite a bit with CAST here, but
I am not having much luck.
Thnx,
Chris.
If I read between the lines, I think you created AccountVolume as a
"BIGINT UNSIGNED" column. Right?
When you subtract something from a 0 BIGINT UNSIGNED column, you are
attempting to make a negative BIGINT UNSIGNED value (which is illegal)
Have you tried casting the column to SIGNED before the subtraction.
Instead of this...
CAST(AccountVolume-2865 AS SIGNED)
Try this
(CAST(AccountVolume AS SIGNED) - 2865)
That should get through the first part of the problem. But you still
need to re-cast the result of the GREATEST function back to an UNSIGNED
value so that it matches the type of the left side of the assignment
operator
(showing any earlier attempts to fix the problem when engaging outside
resources for help can save guessing time)
Another way to avoid this problem is to use something like an IF()
function to avoid going out of range
AccountVolume = IF(AccountVolume > 2865, AccountVolume-2865, 0)
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN
Become certified in MySQL! Visit https://www.mysql.com/certification/
for details.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
