| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with update, please
Alexander,
I can't say if this is the FASTEST way to do this operation, but I've tested the following query on a database and it worked:
UPDATE users
SET userid = SUBSTR(userid, 1, INSTR(userid, '[') - 1)
WHERE userid like '%[%';
Here's my SQL*Plus output when I tried to remove everything after the "1" from all user id's that had "za" and "1..." them:
SQL> select userid from users where userid like 'za%1';
USERID
SQL> update users
2 set userid = SUBSTR(userid, 1, INSTR(userid, '1') - 1)
3 where userid like 'za%1';
2 rows updated
SQL> select userid from users where userid like 'za%1';
USERID
SQL> rollback;
Rollback complete.
SQL> select userid from users where userid like 'za%1';
USERID
Anyway... hope that helps.
--
James Arvigo
Alexander Bibighaus wrote:
I have a database table for which I have to update about 15000 of
its records.
The problem occured because a program that loaded the table had a bug
which was just now discovered. Anyway, here's my problem
I have to update the userid field of all records that match a certain criteria:
matches "* \[*" escape "\" (space followed by [ ) I want to remove everything from that space before the bracket .. to the end.
For instance:
1 alexb [MBID 08/01/98]
2 janedoe
3 johndoe [MBID 08/01/9
4 longusername [M
5 williamjefferson [
Needs to be
1 alexb
2 janedoe
3 johndoe
4 longusername
5 williamjefferson
What is the best way to do this?
I can only think to select out all the data, dump it to a file, run a
perl script,
load it into a temp table, and then update from that temp table.
But because I am dealing with about 15000 records, that is not so desirable :-( Received on Wed Aug 12 1998 - 23:00:56 CDT
 |
 |