jzupet
asked on
HOW TO UPDATE A SELECTED DATE FIELD WITH A NEW DATE
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/M M/YYYY') and (TABLE.COLUMN = 'VALUE'));
select * from TABLE
where (TABLE.COLUMN = to_date('01/01/1999','DD/M
UPDATE TABLE SET DATE_COL= (select date from table )
ASKER
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.
Thanks.
UPDATE TABLE SET DATE_COL= NEW_DATE WHERE TABLE.COLUMN = to_date('01/01/1999','DD/M M/YYYY') and TABLE.COLUMN = 'VALUE';
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/M M/YYYY') and (TABLE.char_COLUMN = 'VALUE'))
LOOP
UPDATE my_TABLE
SET date_COLUMN = to_date('01/01/2007','DD/M M/YYYY') /* New Date */
WHERE rowid = i.row_id;
END LOOP;
COMMIT;
END;
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/M
LOOP
UPDATE my_TABLE
SET date_COLUMN = to_date('01/01/2007','DD/M
WHERE rowid = i.row_id;
END LOOP;
COMMIT;
END;
ASKER
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/M M/YYYY')
WHERE WORKORDER.SCHEDSTART = to_date('30/07/2007','DD/M M/YYYY') and (WORKORDER.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/M
WHERE WORKORDER.SCHEDSTART = to_date('30/07/2007','DD/M
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.