Обсуждение: Getting null values in an update statement
I want to perform an UPDATE to a field in a table. Such a field is of type numeric, does not allow null values and has a default value of 0 (zero). The situation I am facing is that the records that do not meet the update criteria are set to null and I do not know why. I get an error message saying that a violation has been made because the field cannot be set to null. Then, I change the field definition so it accepts null values, run the update statement and I confirm that such a field is set to null for the records that do not meed the criteria. How can I avoid this sitution?
Does this behavior has to do with the way I am using the update statement?
Below is the UPDATE statement for your reference.
I appreciate your help in advance.
Regards,
Jorge Maldonado
UPDATE temp_lista_titulos SET tmt_porc =
(SELECT SUM(tmt_clave) AS suma FROM
(SELECT t1.tmt_clave, t1.tmt_album AS album
FROM temp_lista_titulos as t1, temp_lista_titulos as t2
WHERE t1.tmt_album = t2.tmt_album AND t1.tmt_clave != t2.tmt_clave) temp
WHERE tmt_album = album
GROUP BY tmt_album)
JORGE MALDONADO <jorgemal1960@gmail.com> writes: > I want to perform an UPDATE to a field in a table. Such a field is of type > numeric, does not allow null values and has a default value of 0 (zero). The > situation I am facing is that the records that do not meet the update > criteria are set to null and I do not know why. I get an error message > saying that a violation has been made because the field cannot be set to > null. Then, I change the field definition so it accepts null values, run the > update statement and I confirm that such a field is set to null for the > records that do not meed the criteria. How can I avoid this sitution? > Does this behavior has to do with the way I am using the update statement? > Below is the UPDATE statement for your reference. > UPDATE temp_lista_titulos SET tmt_porc = > (SELECT SUM(tmt_clave) AS suma FROM > (SELECT t1.tmt_clave, t1.tmt_album AS album > FROM temp_lista_titulos as t1, temp_lista_titulos as t2 > WHERE t1.tmt_album = t2.tmt_album AND t1.tmt_clave != t2.tmt_clave) temp > WHERE tmt_album = album > GROUP BY tmt_album) Right offhand I'd guess that the sub-select is finding no rows that match its WHERE clause. SUM() over no rows yields NULL, which is what the SQL spec demands, although IMO anybody with even a nodding acquaintance with math would expect zero. If that's what your problem is, try COALESCE(SUM(...), 0) regards, tom lane