SQLite - query does not work when field is null

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

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).

No, I guess it doesn’t.

Another way:

... WHERE COALESCE(nrc, '') != 'N'

[quote=419405:@Tim Seyfarth]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[/quote]
“DELETE FROM units_accounts WHERE units_accounts.nrc IS NULL”

“DELETE FROM units_accounts WHERE units_accounts.nrc IS NULL OR units_accounts.nrc <> ‘N’”

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

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).

Dim S As String = "DELETE FROM units_accounts WHERE IfNull(units_accounts.nrc,"N") = 'N'"

[quote]
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.[/quote]

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/