CODEWITHSUNDEEP
mysqlsql
Query Master
Query Master

Reputation: 7097

Query update issue

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

Upvotes: 3

Views: 123

Answers (3)

Ike Walker
Ike Walker

Reputation: 65537

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)

Upvotes: 2

Justin Pihony
Justin Pihony

Reputation: 67065

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.

Upvotes: 1

Mike Ryan
Mike Ryan

Reputation: 4374

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.

Upvotes: 0

Related Questions