We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

HOW TO UPDATE A SELECTED DATE FIELD WITH A NEW DATE

jzupet
jzupet asked
on
Medium Priority
342 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'));

Comment
Watch Question

UPDATE TABLE SET DATE_COL= (select date from table )

Author

Commented:
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.
UPDATE TABLE SET DATE_COL= NEW_DATE WHERE  TABLE.COLUMN = to_date('01/01/1999','DD/MM/YYYY') and TABLE.COLUMN = 'VALUE';

Commented:
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;

Author

Commented:
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');


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');

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.