top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to update null into a column type Date in MySQL ?

+1 vote
826 views

I have a table in Mysql DB like following

mysql> describe APPTBL;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| APP_ID      | int(11)     | NO   | PRI | NULL    | auto_increment |
| CREATE_DATE | date        | YES  |     | NULL    |                |
| CLOSE_DATE  | date        | YES  |     | NULL    |                |
| ACTION      | text        | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

While Starting of App, i am inserting the app details with CREATE_DATE and when it is closing, i am updating its CLOSE_DATE. But in case if user is restarting the app, i want to update NULL in place of CLOSE_DATE column. Here is my query,

mysql> update APPTBL set CLOSE_DATE = NULL where APP_ID = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
mysql> select * from APPTBL ;
+-----------+-------------+------------+------------+
|   APP_ID  | CREATE_DATE | CLOSE_DATE |   ACTION   |
+-----------+-------------+------------+------------+
|    1      |  2013-10-08 | NULL       | XXXXXYYYYY |
+-----------+-------------+------------+------------+
1 row in set (0.00 sec)

But why i am not getting any result in this??

mysql> select * from APPTBL where CLOSE_DATE = NULL;
Empty set (0.00 sec)

Also, as i am writhing a java program using this where

rs = st.executeQuery("update APPTBL set CLOSE_DATE = NULL where APP_ID = 1");
if(!rs.wasNull()){
   // Why it is coming here??
}
posted Oct 8, 2013 by Satyabrata Mahapatra

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+1 vote

I found the issue. We can not use wasNull() directly in java like i did previously. By default it will return false only. So, We need to get some value and then have to check whether last value is NULL or not by using wasNull function.

if(rs.next())
    myValue = rs.getString(1);
if(rs.wasNull()){ 
    // Here comes resultset with null value only
}

Thanks for support @Salil

answer Oct 8, 2013 by Satyabrata Mahapatra
0 votes

try IS NULL operator i.e. "select * from APPTBL where CLOSE_DATE IS NULL"

answer Oct 8, 2013 by Salil Agrawal
Similar Questions
+2 votes

How I 'll check how many rows inserted into every second on an average in a table of MySQL database?

+2 votes

In my front end, i am allowing user to create and save multiple number of entries to a table at the same time.

For now i am sending this input to Java class in form of string which i am creating at the time of save. But if table size is very very big(eg. 400-500 entries), then creating string is a time taking process.
Is the any other solution for this?

+2 votes

I have a date field on an html form that users may leave blank. If they do leave it blank I want to write the date 01/01/1901 into the mysql table. How can I accomplish this and where in my .php script file should I put the code?

...