SQLite - query does not work when field is null

  1. 3 weeks ago

    Tim S

    Dec 26 Pre-Release Testers, Xojo Pro Phoenix Arizona USA

    Hello all,
    I am using a simple command to delete all records who's field 'nrc' is not a 'N'.

    Dim S As String = "DELETE FROM units_accounts WHERE units_accounts.nrc = 'N'"

    The problem is, when this field is null, the command does not delete the record. How to work around this?
    Thanks,
    Tim

  2. Kem T

    Dec 26 Pre-Release Testers, Xojo Pro, XDC Speakers New York

    The query says "is 'N'" while your description says "is not a 'N'".

    At any rate, does SQLite support IS DISTINCT FROM / IS NOT DISTINCT FROM? Those will account for NULL (at least in PostgreSQL).

  3. Kem T

    Dec 26 Pre-Release Testers, Xojo Pro, XDC Speakers New York

    No, I guess it doesn't.

    Another way:

    ... WHERE COALESCE(nrc, '') != 'N'
  4. Ralph A

    Dec 26 Santa Monica, California

    @Tim S Hello all,
    I am using a simple command to delete all records who's field 'nrc' is not a 'N'.

    Dim S As String = "DELETE FROM units_accounts WHERE units_accounts.nrc = 'N'"

    The problem is, when this field is null, the command does not delete the record. How to work around this?
    Thanks,
    Tim

    "DELETE FROM units_accounts WHERE units_accounts.nrc IS NULL"

  5. Ralph A

    Dec 26 Santa Monica, California

    "DELETE FROM units_accounts WHERE units_accounts.nrc IS NULL OR units_accounts.nrc <> 'N'"

  6. Tim S

    Dec 26 Pre-Release Testers, Xojo Pro Phoenix Arizona USA

    Hi All

    @ Kem, yes, I wrote that wrong in my post. I'll have a look at COALESCE - never heard of that before.
    @Ralph, I tried the <> 'N' but that still did not delete records which have a null value for that field, will also try your OR suggestion too.

    Thanks all!
    Tim

  7. Tim H

    Dec 26 Pre-Release Testers Portland, OR USA

    The thing about SQL is that any comparison with a NULL value will evaluate to FALSE, regardless of the sense of the comparison - equals or not equals. It will be FALSE always. You have to use the special condition of IS NULL (or NOT IS NULL).

  8. Dave S

    Dec 26 San Diego, California USA
    Dim S As String = "DELETE FROM units_accounts WHERE IfNull(units_accounts.nrc,"N") = 'N'"
  9. Maximilian T

    Dec 29 Pre-Release Testers, Xojo Pro Europe, Germany, Berlin

    The thing about SQL is that any comparison with a NULL value will evaluate to FALSE, regardless of the sense of the comparison - equals or not equals. It will be FALSE always.

    Actually no, the result will be NULL not false.
    select (4 = NULL);
    returns NULL, not false. There is an interesting article about the whole NULL topic at: https://www.cybertec-postgresql.com/en/a-postgresql-story-about-null-is-null-null-and-not-null/

or Sign Up to reply!