How to update 55 thousand rows with different values in single update query?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

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

  1. #1
    Join Date
    Dec 2000
    Posts
    95

    Question 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

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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!

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    ===========
    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
  •  



Click Here to Expand Forum to Full Width