oracle9i queries

Question

I am having a oracle table consisting of 6 columns,out of which one is date column. The date field has null entries in some rows...no other field is null.. Can I delete the rows having date field as null? or should I update some value in the date field for those rows...?

Please provide me the correct way to do both these operations?

thanks....

Answer 1

delete from table_name where date_field is null;

Answer 2

If you need to retain the other values in the row you can provide a default value for the date in a query with;

SELECT NVL(dateCol, to_date('31/01/2009','dd/MM/yyyy')) FROM dataTable

To update the null values in the table use;

UPDATE dataTable
SET dateCol = to_date('31/01/2009','dd/MM/yyyy') 
WHERE dateCol IS NULL

To remove the null rows;

DELETE FROM dataTable
WHERE dateCol IS NULL

Answer 3

Correct answers have already been given. I'm only adding a bit of explanation here. Comparing using the equality operator (=) and NULL always gives NULL, never TRUE. That's why you have to use "IS NULL" instead of "= NULL". Here is the part of the documentation that discusses NULLS: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements005.htm#i59110

An example:

SQL> create table mytable (col1,col2,col3,col4,col5,datecol)
  2  as
  3  select 1,1,1,1,1,sysdate from dual union all
  4  select 2,2,2,2,2,null from dual union all
  5  select 3,3,3,3,3,null from dual
  6  /

Tabel is aangemaakt.

SQL> delete mytable
  2   where datecol = null
  3  /

0 rijen zijn verwijderd.

SQL> select * from mytable
  2  /

 COL1  COL2  COL3  COL4  COL5 DATECOL
----- ----- ----- ----- ----- -------------------
    1     1     1     1     1 03-05-2009 11:36:08
    2     2     2     2     2
    3     3     3     3     3

3 rijen zijn geselecteerd.

SQL> delete mytable
  2   where datecol is null
  3  /

2 rijen zijn verwijderd.

SQL> select * from mytable
  2  /

 COL1  COL2  COL3  COL4  COL5 DATECOL
----- ----- ----- ----- ----- -------------------
    1     1     1     1     1 03-05-2009 11:36:08

1 rij is geselecteerd.

Regards, Rob.