Query update issue

Question

I m just confuse in this Query and i don't know how to solve this if you have any idea about this please help me or helps are definitely appreciated

I have table structure like this and test column contain 3 value

enter image description here

UPDATE `test` SET test = test -3

when i execute this Query the result will be show like this

enter image description here

UPDATE `test` SET test = test -4

But when i execute this query the result will not proper save in test column like this

enter image description here

0 result required or i don't need any subtract value also

well bigint is made for positive integers, so below 0 it starts at the very top again — Hajo, Apr 19 '12 at 16:43
@Hajo http://sqlfiddle.com/#!2/9802b/1 This fiddle says otherwise. That is what I thought, but wanted to test it out — Justin Pihony, Apr 19 '12 at 16:45
@JustinPihony well such things sometimes depend on the implementation of the rdbms used — Hajo, Apr 19 '12 at 16:48
@Samad - It's better to see the table definition. For example, we don't know if the bigint is signed or not. — dcp, Apr 19 '12 at 16:51

Ike Walker · Accepted Answer · 2012-04-19T17:01:34.533

2

Apparently you are using the BIGINT UNSIGNED data type. If you want to store negative numbers, you need to change it to a regular signed BIGINT (Also be sure to set it to NULL or NOT NULL as required):

ALTER TABLE test
  MODIFY COLUMN test BIGINT;

UPDATE: If you actually want to store 0 instead of -4 in your example, you can do so using the GREATEST() function like this:

UPDATE `test` SET test = GREATEST(CAST(test AS SIGNED) - 4,0)

edited Apr 19 '12 at 17:01

answered Apr 19 '12 at 16:53

Ike Walker

64,401
14
110
109

Thanx for share your exprience @Ike Walker – Query Master Apr 19 '12 at 17:10

Justin Pihony · Answer 2 · 2012-04-19T17:00:31.723

The problem is most likely due to the fact that your bigint is unsigned.

Per the documentation, an unsigned bigint is

A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

Notice the unsigned range and how it is your number - 3 (4 comes from getting from 0 to xxx15 I believe)

So, you should only need to update your column to be a bigint that is not unsigned (signed) and this should work.

ALTER TABLE test MODIFY COLUMN test BIGINT SIGNED;

UPDATE

If you want to keep the BIGINT UNSIGNED so that you cannot have negative numbers, then you could write a trigger to force a 0, or you could just make your query something like this:

UPDATE test
SET test = CASE WHEN test >= value THEN test-value ELSE 0 END

Basically, if the value attempting to be subtracts is more than the current value, then just set the value to 0, otherwise perform the subtraction.

@JustinPihony without trigger is possible – Query Master Apr 19 '12 at 17:07 — Query Master, Apr 19 '12 at 17:07

score 0 · Answer 3 · edited May 23 '17 at 12:19

0

As per this question: MySQL: bigint Vs int

bigint's maximum value is 18,446,744,073,709,551,615

You're wrapping around to the highest value when you subtract from 0, since your bigint is unsigned.

edited May 23 '17 at 12:19

Community

1
1

answered Apr 19 '12 at 16:46

Mike Ryan

4,234
1
19
22

Query update issue

3 Answers3