efficient update program
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: efficient update program

  1. #1
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639

    efficient update program

    Hi Friends,

    I have a table emp with columns (hiredate date, birthdate date);
    I want to update the two columns set to NULL if their value is
    greater than sysdate. Like this:

    sql> update emp set hiredate=null where hiredate>sysdate;
    sql> update emp set birthdate=null where birthdate>sysdate;

    How can i write this on one command line (maybe using decode)
    so i wont re-read the table again, thus making it efficient.
    I want one pass to the table.

    Thanks a lot

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Code:
    update emp 
      set hiredate = 
                      case when hiredate > sysdate then 
                          null 
                      else hiredate end, 
          birthdate = 
                      case when birthdate > sysdate then 
                          null 
                      else birthdate end 
    where 
      birthdate > sysdate or hiredate > sysdate
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Thanks hun, hmmnnn new look in sqlplus command. it looks like
    pl/sql...i learned a new technique again, if u want to evaluate/validate all columns you can use CASE.

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