Home Where and how to correctly write a condition in an UPDATE query in postgresql?

Questions

Where and how to correctly write a condition in an UPDATE query in postgresql?

October 14, 2022

There was a moment where I need to calculate the data, but there are empty fields (null) and I wanted him to skip if there is nothing in some fields. Tell me how to prescribe it, otherwise I get an error

ERROR: division by zero
SQL state: 22012

sql query:

UPDATE summary_all_filter_edit SET resul_power = (SELECT round((((all_plats_ok::numeric - all_plats_no::numeric) / all_plats_ok::numeric) * power::numeric), 2) from summary_all_filter_edit su where su.id=summary_all_filter_edit.id);

My table: https://dbfiddle.uk/cXyIdMBr

>Solution :

You may try adding a check in the WHERE clause which prevents an update in the case where the denominator be zero:

UPDATE summary_all_filter_edit
SET resul_power = (
    SELECT round((((all_plats_ok::numeric - all_plats_no::numeric) /
                    all_plats_ok::numeric) * power::numeric), 2)
FROM summary_all_filter_edit su
WHERE su.id = summary_all_filter_edit.id AND
      all_plats_ok::numeric != 0);