need help in update statement
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: need help in update statement

  1. #1
    Join Date
    Jun 2008
    Posts
    38

    need help in update statement

    i have created a table with the system date as one of the column:

    create table nav_date as (SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') "Current date" FROM dual)

    ------------------------------------------------------

    how to get the current system date in the table whenever i open the table nav_date?

    I mean the table should automatically update the date.

  2. #2
    Join Date
    Feb 2004
    Location
    UK
    Posts
    56
    create a view
    select sysdate as nav_date
    from dual ?

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by ali560045
    i have created a table with the system date as one of the column:

    create table nav_date as (SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') "Current date" FROM dual)

    ------------------------------------------------------

    how to get the current system date in the table whenever i open the table nav_date?

    I mean the table should automatically update the date.
    why dont you just query from dual?

  4. #4
    Join Date
    Jun 2008
    Posts
    38
    when i m creating the table for the first time i m doing sysdate from dual only.

    But when i open(select * from nav_date) the table say after 5 mins , it should show me the updated time and not the pevious stored time.

    so not getting how to achive this?

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    again, instead of selecting from your table - why not just select from dual. What you are doing makes no sense

  6. #6
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    339
    Quote Originally Posted by citldba
    create a view
    select sysdate as nav_date
    from dual ?

    like citldba said

    you create a view

    create view time_view as ( SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') "Current date" FROM dual);

    it will get update.
    In the case of table, it is created with the time while you firing the create query.so when you select from the table you will get the time already stored at the time of creation,again it wont go to select from the dual.

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    AGAIN - WHY?

    whay create a view on dual - whats the point!

    And why do you think it wont select from dual - wtf do you think the view is doing?

  8. #8
    Join Date
    Jun 2008
    Posts
    38
    thanks a lot. i tink this view seems to help me for this time now.

    thanks all of u for ur precious time

  9. #9
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    339
    Quote Originally Posted by davey23uk
    AGAIN - WHY?

    whay create a view on dual - whats the point!

    And why do you think it wont select from dual
    dave,

    when i tried it from the table it is not doing.But it is working fine in the view.
    can you post the correct query for the table ??

    [/QUOTE]
    - wtf do you think the view is doing?[/QUOTE]

    yes the view is doing

    SQL> select * from time_view;

    Current date
    --------------------
    24-JUN-2008 06:08:03

    SQL> /

    Current date
    --------------------
    24-JUN-2008 06:08:07

    SQL> /

    Current date
    --------------------
    24-JUN-2008 06:08:09
    Last edited by gopu_g; 06-24-2008 at 08:48 AM.

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    select sysdate from dual - will return the date

    its up to the CLIENT to say how it wants it displayed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width