Home » SQL & PL/SQL » SQL & PL/SQL » update/replace in pl/sql block (Oracle 9i, Unix)
update/replace in pl/sql block [message #298631] Wed, 06 February 2008 21:38 Go to next message
Cathy2008
Messages: 9
Registered: February 2008
Junior Member
I have a table which has more than 45000 rows and needs to update a particular coulumn. The column values are like below

/myserver/myfolder/mysubfolder/xyz/file1
/myserver/myfolder/mysubfolder/xyz/file2
/myserver/myfolder/mysubfolder/xyz/file3
/myserver/myfolder/mysubfolder/xyz/file3
.
.
.

i want to update the column values as below...

\\Server1\folderx\foldery\file1
\\Server1\folderx\foldery\file2
\\Server1\folderx\foldery\file3
\\Server1\folderx\foldery\file4
.
.
.

Instead of writing 45000 update SQL statements, can some one tell me how to write a simeple pl/sql block.

appreciate your help.
Re: update/replace in pl/sql block [message #298632 is a reply to message #298631] Wed, 06 February 2008 21:52 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
DECLARE
CURSOR c1 IS SELECT column_name FROM table_name;
BEGIN
FOR item IN c1
LOOP
(put here your update command)
END LOOP;
END;



regards,
Re: update/replace in pl/sql block [message #298634 is a reply to message #298631] Wed, 06 February 2008 22:27 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Would normally say check the SQL Reference for substr/instr or regexp, but I felt a little gratuitous this moment.

You can perform the operation with just one SQL command.

SQL> select * from tab1;

FILEPATH
--------------------------------------------------
/myserver/myfolder/mysubfolder/xyz/file1
/myserver/myfolder/mysubfolder/xyz/file2
/myserver/myfolder/mysubfolder/xyz/file3
/myserver/myfolder/mysubfolder/xyz/file4

SQL> update tab1
  2  set filepath = '\\Server1\folderx\foldery\'||substr(filepath, instr(filepath, '/', -1) + 1);

4 rows updated.

SQL> select * from tab1;

FILEPATH
--------------------------------------------------
\\Server1\folderx\foldery\file1
\\Server1\folderx\foldery\file2
\\Server1\folderx\foldery\file3
\\Server1\folderx\foldery\file4

SQL> rollback;

Rollback complete.

SQL> -- Versions 10g and above
SQL>
SQL> update tab1
  2  set filepath = '\\Server1\folderx\foldery\'||regexp_substr(filepath, '[^/]*$');

4 rows updated.

SQL> select * from tab1;

FILEPATH
--------------------------------------------------
\\Server1\folderx\foldery\file1
\\Server1\folderx\foldery\file2
\\Server1\folderx\foldery\file3
\\Server1\folderx\foldery\file4

SQL>
Re: update/replace in pl/sql block [message #298788 is a reply to message #298631] Thu, 07 February 2008 13:00 Go to previous messageGo to next message
Cathy2008
Messages: 9
Registered: February 2008
Junior Member
Thank you for you quick response.

I have written an update statement, which is working.

Update tablename set column1 = replace (column1,'/myserver/myfolder/mysubfolder/xyz/','\\Server1\folderx\foldery\')
where column1 is not null
and column1 like '/myserver/myfolder/mysubfolder/xyz%';


Thanks again.
Re: update/replace in pl/sql block [message #298800 is a reply to message #298788] Thu, 07 February 2008 14:39 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Absolutely. A simple replace will work if all the paths are static.
Previous Topic: Oracle Load processes
Next Topic: Converting a varchar2 to a char
Goto Forum:
  


Current Time: Thu Dec 08 20:37:00 CST 2016

Total time taken to generate the page: 0.06169 seconds