Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in

Question

I have a query:

update `shops` set
    `points` = `points` - 2,
    `updated_at` = '2019-04-17 23:07:11'
where `id` = 4;

Column points have a column type: BIGINT(20).

Now in record I have value 62. When I run the above query I get this error:

SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in '(`database`.`shops`.`points` - 2)'

Is different.

https://stackoverflow.com/questions/11698613/bigint-unsigned-value-is-out-of-range-my-sql — Nikhil S, Apr 17 '19 at 20:13
Possible duplicate of [BIGINT UNSIGNED VALUE IS out of range My SQL](https://stackoverflow.com/questions/11698613/bigint-unsigned-value-is-out-of-range-my-sql) — Nikhil S, Apr 17 '19 at 20:14
I would contend that you are hitting a record that is pulling a default value of `0` before the subtraction, making -2 an invalid unsigned integer. — Shawn, Apr 17 '19 at 20:17
_BIGINT **UNSIGNED**_ cannot store negative values. Perhaps subtraction resulted in a negatve value. — Salman A, Apr 17 '19 at 20:17
...`CAST(\`shops\` set \`points\` = \`points\` - 2 AS SIGNED)`... — l'L'l, Apr 17 '19 at 20:18
That would work to see it, but then it still couldn't insert the value back into the column if it's an unsigned datatype — Shawn, Apr 17 '19 at 20:20
True, might even be able to handle the issue with `- 2.0` instead of `- 2` — l'L'l, Apr 17 '19 at 20:24
https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=e9597f4b4d072cf72f5906fe0eb61c14 — Shawn, Apr 17 '19 at 20:40
`"Column points have a column type: BIGINT(20)."` Your error message indicates that `points` is actually a `BIGINT(20) UNSIGNED` type. — Shawn, Apr 17 '19 at 20:46

Nikhil S · Accepted Answer · 2019-04-17T20:27:36.283

3

this will work:

 set `points` = `points` - cast(2 AS SIGNED)

and

`updated_at` = '2019-04-17 23:07:11'

edited Apr 17 '19 at 20:27

answered Apr 17 '19 at 20:22

Nikhil S

2

Not sure how that would work. It would still try to insert a -2 into a column that is an `unsigned` datatype. https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=8d07f56083557e4b336374dc02ddb77d – Shawn Apr 17 '19 at 20:43

Salman A · Answer 2 · 2019-04-18T07:07:21.783

3

You cannot store negative values in an unsigned integer. The safer solution is to check the operands before performing the subtraction:

SET points = CASE WHEN points >= 2 THEN points - 2 ELSE 0 END

Or simply:

SET points = points - LEAST(points, 2)

edited Apr 18 '19 at 07:07

answered Apr 17 '19 at 20:24

Salman A

Or: `UPDATE shops SET points = points - 2, updated_at = '2019-04-17 23:07:11' WHERE id = 4 AND points >= 2;` – Usagi Miyamoto Apr 18 '19 at 06:57

2 Answers2