?
Solved

HOW TO UPDATE A SELECTED DATE FIELD WITH A NEW DATE

Posted on 2007-07-19
6
Medium Priority
?
319 Views
Last Modified: 2013-12-19
Hi, I would like to add to the following select statement (unless a better way is provided) to update the column with a different date. Here's the select statement, I am unclear on how to 'set' new date value in column. Thank you anybody!

select * from TABLE
where (TABLE.COLUMN = to_date('01/01/1999','DD/MM/YYYY') and (TABLE.COLUMN = 'VALUE'));

0
Comment
Question by:jzupet
  • 3
  • 2
6 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 19526115
UPDATE TABLE SET DATE_COL= (select date from table )
0
 

Author Comment

by:jzupet
ID: 19526216
sathyagiri, I need the select statement to mine the database for a specific date first, then update those records found with a new date. I dont see that in your response.
Thanks.
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 19526360
UPDATE TABLE SET DATE_COL= NEW_DATE WHERE  TABLE.COLUMN = to_date('01/01/1999','DD/MM/YYYY') and TABLE.COLUMN = 'VALUE';
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:YANN0S
ID: 19526401
you need to use PL/SQL

One way to do it is

BEGIN
FOR i IN
      (select tab.*, tab.rowid row_id from my_TABLE tab
           where (TABLE.date_COLUMN = to_date('01/01/1999','DD/MM/YYYY') and (TABLE.char_COLUMN = 'VALUE'))
LOOP
         UPDATE my_TABLE
                SET date_COLUMN = to_date('01/01/2007','DD/MM/YYYY') /* New Date */
             WHERE rowid = i.row_id;
END LOOP;
COMMIT;
END;
0
 

Author Comment

by:jzupet
ID: 19527159
Yannos, I'm a little confused on the PL/SQL. My table name is WORKORDER, column name hosting date is SCHEDSTART and column i'm checking for matches is STATUS = 'SKED'.

Sathyagiri, I tried the following and it fails, no updates to my record. If the column data includes a time (7:00:00 AM), would this keep the record from updating? Anyways, here's the statement I tried:

UPDATE WORKORDER SET WORKORDER.SCHEDSTART = to_date('01/01/2009','DD/MM/YYYY')
   WHERE WORKORDER.SCHEDSTART = to_date('30/07/2007','DD/MM/YYYY') and (WORKORDER.STATUS = 'SKED');


0
 
LVL 14

Accepted Solution

by:
sathyagiri earned 500 total points
ID: 19527193
Yes it will, if it has time component stored.

Try this one

UPDATE WORKORDER SET WORKORDER.SCHEDSTART = to_date('01/01/2009','DD/MM/YYYY')
  WHERE trunc(WORKORDER.SCHEDSTART) = to_date('30/07/2007','DD/MM/YYYY') and (WORKORDER.STATUS = 'SKED');
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses
Course of the Month17 days, 8 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question