Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

updating a table using a to_char date

user13328581Jan 26 2011 — edited Jan 26 2011
Dear all;

I have the following query and table below
create table t1
( 
       cid varchar2(200),
       yearid varchar2(200),
       begin_date date
       );

 insert into t1
           (cid, yearid, begin_date)
         values
           ('A', '2010_1', sysdate);
  insert into t1
           (cid, yearid, begin_date)
         values
           ('B', '2010_1', to_date('03/04/2011', 'MM/DD/YYYY'));
insert into t1
           (cid, yearid, begin_date)
         values
           ('C', '2010_1', to_date('01/02/2011', 'MM/DD/YYYY'));
and I have this update statement below
   update t1 t
       set t.yearid = '2011_1'
       where to_char(t.begin_date, 'YYYY') = substr(t.yearid, 1, 4);
what i am trying to is basically update table t1 yearid which is a varchar2 where it has to_char begin date that are irregular...so in my sample case,

to_char of the begin date return a 2011
hence, the yearid should be 2011_1 instead.

However, my udate statement isnt working though
This post has been answered by Solomon Yakobson on Jan 26 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 23 2011
Added on Jan 26 2011
4 comments
3,425 views