-
How to update 55 thousand rows with different values in single update query?
Hi All,
I need to update the username of the user table with the new username. The new username and the existing username mapping is there in the UserMappings table. I need to update 55 thousand usernames. I have tried with the below query, but its throwing an error as "ORA-25153 : Temporary Tablespace is Empty".
update user u
set u.userName=
(select newUserName from UserMappings where oldUserName = u.userName)
where u.userName in (select oldUserName from UserMappings)
Is there any other approach to update the 55 thousand rows, instead of changing the Temporary tablespace?
Thanks in advance
regards,
Srinivas Medukonduru
-
You seem to have lost your temp files!
ORA-25153 Temporary Tablespace is Empty
Cause: An attempt was made to use space in a temporary tablespace with no files.
Action: Add files to the tablespace using ADD TEMPFILE command.
Tamil showed me how to do this kind of update with an in-line view - elegant, fast but a bit confusing at first sight. In the end I came round to appreciate his method:
http://www.dbasupport.com/forums/sho...threadid=42143
but I suspect you'd better fix the temp problem first!
-
===========
update user u
set u.userName=
(select newUserName
from UserMappings
where oldUserName = u.userName)
where u.userName in
(select oldUserName from UserMappings)
===========
From the update statement, I assume oldusername is unique in usermappings table, and username in user is also unique.
If both are unique and unique indexes (PK) exist, you can simply use in-line view update.
Code:
Update ( select a.username old_name ,
b.newusername new_name
from user a,
usermappings b
where a.username = b.oldusername )
set old_name = new_name ;
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|