0

I am trying to run an update in a table and I get an "Error Code: 1365. Division by 0". The sql commands I use are these:

SET @@autocommit = 0;
start transaction;
update XE_General
join Locations
on XE_General.url_location = Locations.url_location
set 
XE_General.neighborhood = Locations.neighborhood_GR,
XE_General.municipality = Locations.municipality_GR,
XE_General.zipcode = Locations.zipcode 
where XE_General.zipcode is null
and XE_General.neighborhood is null
and XE_General.municipality is null;

I do not use division anywhere in the query and I have checked that rows which satisfy the where conditions indeed exist.

What could be the cause of this error? Thank you in advance for any suggestions.

3
  • Do you have triggers that could be dividing by zero when you update? Commented Nov 16, 2021 at 10:59
  • 1
    Are either of those "Tables" actually /Views/ that might contain such calculations?
    – Phill W.
    Commented Nov 16, 2021 at 11:01
  • 1
    @WillemRenzema there is one generated column in the XE_General table which uses division. Is this somehow related?
    – Elena Mina
    Commented Nov 16, 2021 at 11:08

1 Answer 1

1

If you have a generated column that is STORED, then

Column values are evaluated and stored when rows are inserted or updated. A stored column does require storage space and can be indexed.

This would explain the why the error occurs while updating. Whatever values you are setting can cause the generated column to divide by zero at the same time as the update.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.