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!

Quote:

---

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