delete null records

[babeo]

Hi,

I want to delete null records, the problem is when I try to query * (all) records of the table so that I can see the null records and delete it, but the null records does not show up. How can I delete it. I already tried:
> delete from TableName where FiedlA = " " (also "&quot
> delete from TableName where FieldA is null
> select * from TableName where FieldA is null
> select * from TableName where FieldA = "" (also " &quot

Thanks

 

[saikrish]

Hi,
A 'NULL' represents an unknown or inapplicable value. So there is no way that it can be matched using expressions like "" or " ". I don't see anything strange with your second delete statement and the first select statement. The 'from' keyword in the delete statement is optional. So you could either use 'delete from tablex' or simply 'delete tablex' followed by the appropriate where conditions. Also you can check for equality of 'NULL' just as you would for any number. I mean 'delete tablex where FieldA = NULL'.
If possible, check if there is any case-sensitivity setting active in your database server. Try using NULL instead of null as you have done earlier. If it doesn't solve your problem still, can you quote the error/warning messages if you get any.

Vijay

 

[babeo]

Hi Vijay,

Thanks for correction.
I try with NULL (not null), actually both again, but still the return message is "0 rows affected" for both select and delete commands.

However, I can tell that there is still an empty record in the database because there is another process checking/referencing to these tables and because it finds the empty records it keeps warning the field of those tables are error.

The following are the messages generated by the process mentioned:
Error: School_Key: UNKNOWNED for School_ID [null]
Error: Prov_Key: UNKNOWNED for Province_ID [null]

Thanks