Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2011
    Posts
    8

    Unanswered: 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, 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 confirmed that such a field is set to null for the records that do not meed the criteria. How can I avoid this sitution?
    Below is the UPDATE statement for your reference. I think that the problem appears when the sub-select returns no rows which generates a null value.
    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)

    I tried the following change without success:
    SELECT CASE WHEN SUM(tmt_clave) isnull THEN 0 ELSE SUM(tmt_clave) END AS suma FROM
    Last edited by JORGEMAL; 04-26-11 at 20:30.

  2. #2
    Join Date
    May 2011
    Posts
    1
    I think you can accomplish this using COALESCE(), which if you're not familiar will return the first argument that is not null.

    PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Conditional Expressions

    Code:
    UPDATE temp_lista_titulos SET tmt_porc =
    COALESCE((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), 0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •