Another SQL question

I want to delete a record from a table where there is something written in a specific column and another specific column must be empty. Can I query for ‘NIL’ ?

like: “delete from This_Table where This_Field = ‘9999’ and That_Field = NIL” or
or: “delete from This_Table where This_Field = ‘9999’ and That_Field = ‘NIL’” or is it correct to do:

“delete from This_Table where This_Field = ‘9999’ and That_Field = ‘’”

Depends what SQL I guess
For Postgresql
DELETE FROM This_Table WHERE This_Field = ‘9999’ AND That_Field IS NULL;

Hi Dirk,

SQlite

same. test with IS NULL

= “” works

Does that also work when the value is NULL and therefore undefined?
I have never tested that with SQLite since we use PostgreSQL. If I were to test for =’’ in Postgresql, that would be false for the records where the field is not filled in and therefore has no value (therefore IS NULL). Solution for that in Postgresql would be to define the field as not being allowed to be NULL and giving it a default value of ‘’. Then I would be able to test with =’’. Otherwise I’d be forced to test with IS NULL.

Yes it does. In my Sqlite editor I see the column has a NIL value.

Cool. Good to know. Like I said things are different between SQL implementations. Always difficult to guess for an SQL syntax that is different to what one is used to.

Not a good idea. While this might work in SQLite it is a BAD HABIT to get into.

Most major SQL engines treat “” (an empty string) and NIL/NULL (a missing string) as TWO different values and treats them that way.

OK. Thanks. I have a query that returns a recordset but all the fields are NIL so to check wether RS<>Nil does not compute.

How to deal with this?

use the ISNULL operator

OK… so

if not rs.field("ID-FIELD").value.isnull then

etc…