How to update 55 thousand rows with different values in single update query?
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
(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
You seem to have lost your temp files!
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:
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.
but I suspect you'd better fix the temp problem first!
update user u
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 ,
from user a,
where a.username = b.oldusername )
set old_name = new_name ;
Click Here to Expand Forum to Full Width