-1

I'm migrating a column from one sql table to another with update command. While updating with below query, i get the ORA-01722: invalid number error. The issue is PID field in project table has varchar2 datatype and xproject_id in docmeta table has number data type. what options do i have to migrate this data now.

BEGIN
FOR X  IN(select projecttype,pid from PROJECT)  
 loop
  update docmeta d
set d.xProjectType=X.projecttype
where d.xproject_id=X.PID
and X.projecttype is not null;
END LOOP;
END;
Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69
Pulkit Bhatia
  • 127
  • 1
  • 7
  • 22
  • 2
    The reason is obvious: you joining columns with different datatype, so one of them might have any value that is not necessarily can be converted to number. The question is, what you want to do with such non-numeric values? – Yaroslav Shabalin Mar 06 '14 at 10:03
  • True. As i already said, The issue is PID field in project table has varchar2 datatype and xproject_id in docmeta table has number data type. How do i update projectttype now as i'm getting the error while i run the above query? – Pulkit Bhatia Mar 06 '14 at 11:05
  • 1
    There is a lot of methods to check if value is numeric or not. See these questions to choose most appropriate for you: [#1](http://stackoverflow.com/q/5082176/2899629), [#2](http://stackoverflow.com/q/7957423/2899629), [#3](http://stackoverflow.com/q/5666986/2899629). – Yaroslav Shabalin Mar 06 '14 at 11:12

1 Answers1

1

You can catch the exception:

begin
   for x in (select projecttype
                   ,pid
               from project
              where procecttype is not null)
   loop
      begin
         update docmeta d
            set d.xprojecttype = x.projecttype
          where d.xproject_id = x.pid;
      exception
         when others then
            if sqlcode = -1722
            then
               null;
            else
               raise;
            end if;
      end;
   end loop;
end;

Also note that I have moved the filter on non-null projecttype to the query.

Rene
  • 10,391
  • 5
  • 33
  • 46