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

UPDATE `test` SET test = test -3

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

UPDATE `test` SET test = test -4

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

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

Answer 1

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)

Answer 2

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.