How to update 55 thousand rows with different values in single update query?

[srinivasm]

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

[DaPi]

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!

[tamilselvan]

===========
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.

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