This documentation relates to the latest version of Confluence.
If you are using an earlier version, please go to the documentation home page and select the relevant previous version.

Changing Usernames

Added by Matt Ryall, last edited by Roy Hartono on Apr 20, 2008  (view change)

A username is the name used to login to Confluence, eg. jsmith. There is no support for changing a username via Confluence yet, but you can to vote towards a feature request to allow usernames to be changed from the web interface.

Instructions For Changing Usernames

Usernames can only be changed through direct update to the Confluence database.

  1. If you have a database administrator, request that they approve the database-related steps described below

  2. If you are using JIRA user management, Revert from JIRA To Internal User Management

  3. Backup Confluence

  4. Creating a usermigration table: 
    create table usermigration
(
oldusername varchar,
newusername varchar
)
  5. Usernames that will be changed must be placed in the usermigration table with their current and planned usernames: 
    insert into usermigration (oldusername, newusername)
values ('oldusername', 'newusername');
  6. If your DB administration tool does not support multiple SQL queries, these must be entered individually. 
    update attachments
set creator = newusername from usermigration u
where creator = u.oldusername;

update attachments
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update content
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update content
set creator = newusername from usermigration u
where creator = u.oldusername;

update content
set username = newusername from usermigration u
where username = u.oldusername;

update extrnlnks
set creator = newusername from usermigration u
where creator = u.oldusername;

update extrnlnks
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update links
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update links
set creator = newusername from usermigration u
where creator = u.oldusername;

update notifications
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update notifications
set creator = newusername from usermigration u
where creator = u.oldusername;

update pagetemplates
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update pagetemplates
set creator = newusername from usermigration u
where creator = u.oldusername;

update spaces
set creator = newusername from usermigration u
where creator = u.oldusername;

update spaces
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update spacepermissions
set permusername = newusername from usermigration u
where permusername = u.oldusername;

update spacepermissions
set creator = newusername from usermigration u
where creator = u.oldusername;

update spacepermissions
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update contentlock
set creator = newusername from usermigration u
where creator = u.oldusername;

update contentlock
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;

update os_user
set username = newusername from usermigration u
where username = u.oldusername;

update trackbacklinks
set creator = newusername from usermigration u
where creator = u.oldusername;

update trackbacklinks
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;
  7. If using Confluence 2.1 or newer, run the following command: 
    update users
set name = newusername from usermigration u
where name = u.oldusername;
  8. Reassign personal spaces associated with the old username to the new username. The tilda (~) is required as it is prepended to the space key of all personal spaces. 
    update spaces
set spacekey = '~newusername'
where spacekey = '~oldusername';
  9. Each username is associated with a full name. For example, username 'jsmith' may have a full name of 'John M Smith'. If this fullname needs to be changed, modify the fullname in the users or os_user table.

All old usernames in Confluence should now be replaced with the new usernames from the usermigration table.

RELATED TOPICS
Adding a Group
Adding a New User
Adding or Removing a User from a Group
Changing Usernames
Editing User Details

Labels

 

Comments (19)

 

  1. Aug 22, 2006

    Donald Jennings says:

    Not sure how up to date the SQL above is for this problem. Below is the SQL I ha...

    Not sure how up to date the SQL above is for this problem. Below is the SQL I had to use to fix a naming problem for users as we moved from one LDAP service to another (their 6+2 login names were slightly different in some cases).

    begin
    for i in (select * from usermigration)
    loop

    delete from external_entities
    where name = i.newusername;

    update external_entities
    set name = i.newusername
    where name = i.oldusername;

    update attachments
    set creator = i.newusername
    where creator = i.oldusername;

    update attachments
    set lastmodifier = i.newusername
    where lastmodifier = i.oldusername;

    update content
    set lastmodifier = i.newusername
    where lastmodifier = i.oldusername;

    update content
    set creator = i.newusername
    where creator = i.oldusername;

    update content
    set username = i.newusername
    where username = i.oldusername;

    update content_label
    set owner = i.newusername
    where owner = i.oldusername;

    update draft
    set owner = i.newusername
    where owner = i.oldusername;

    update extrnlnks
    set creator = i.newusername
    where creator = i.oldusername;

    update extrnlnks
    set lastmodifier = i.newusername
    where lastmodifier = i.oldusername;

    update links
    set lastmodifier = i.newusername
    where lastmodifier = i.oldusername;

    update links
    set creator = i.newusername
    where creator = i.oldusername;

    update notifications
    set username = i.newusername
    where username = i.oldusername;

    update notifications
    set lastmodifier = i.newusername
    where lastmodifier = i.oldusername;

    update notifications
    set creator = i.newusername
    where creator = i.oldusername;

    update pagetemplates
    set lastmodifier = i.newusername
    where lastmodifier = i.oldusername;

    update pagetemplates
    set creator = i.newusername
    where creator = i.oldusername;

    update spaces
    set creator = i.newusername
    where creator = i.oldusername;

    update spaces
    set lastmodifier = i.newusername
    where lastmodifier = i.oldusername;

    update spacepermissions
    set permusername = i.newusername
    where permusername = i.oldusername;

    update spacepermissions
    set creator = i.newusername
    where creator = i.oldusername;

    update spacepermissions
    set lastmodifier = i.newusername
    where lastmodifier = i.oldusername;

    update contentlock
    set creator = i.newusername
    where creator = i.oldusername;

    update contentlock
    set lastmodifier = i.newusername
    where lastmodifier = i.oldusername;

    --update emailtemplates
    --set creator = i.newusername
    --where creator = i.oldusername;

    --update emailtemplates
    --set lastmodifier = i.newusername
    --where lastmodifier = i.oldusername;

    --update emailtemplates
    --set username = i.newusername
    --where username = i.oldusername;

    update os_user
    set username = i.newusername
    where username = i.oldusername;

    update trackbacklinks
    set creator = i.newusername
    where creator = i.oldusername;

    update trackbacklinks
    set lastmodifier = i.newusername
    where lastmodifier = i.oldusername;

    update OS_PROPERTYENTRY
    set entity_name = replace(entity_name,i.oldusername,i.newusername)
    where entity_id = (select entity_id from external_entities where entity_name = i.newusername);

    end loop;
    end;

  2. Sep 11, 2006

    Frank Stiller says:

    The Article is outdated. As Donald wrote, there are some new Tables which are n...

    The Article is outdated.

    As Donald wrote, there are some new Tables which are not mentioned. Here is my SQL-Code running in Version 2.2.8, with MySQL 4.1:

    update attachments x inner join usermigration u on
    x.creator = u.oldusername
    set x.CREATOR = u.newusername;

    update attachments x inner join usermigration u on
    x.lastmodifier = u.oldusername
    set x.lastmodifier = u.newusername;

    update content x inner join usermigration u on
    x.lastmodifier = u.oldusername
    set x.lastmodifier = u.newusername;

    update content x inner join usermigration u on
    x.creator = u.oldusername
    set x.creator = u.newusername;

    update content x inner join usermigration u on
    x.username = u.oldusername
    set x.username = u.newusername;

    update content_label x inner join usermigration u on
    x.owner = u.oldusername
    set x.owner = u.newusername;

    update draft x inner join usermigration u on
    x.owner = u.oldusername
    set x.owner = u.newusername;

    update extrnlnks x inner join usermigration u on
    x.creator = u.oldusername
    set x.creator = u.newusername;

    update extrnlnks x inner join usermigration u on
    x.lastmodifier = u.oldusername
    set x.lastmodifier = u.newusername;

    update label x inner join usermigration u on
    x.owner = u.oldusername
    set x.owner = u.newusername;

    update links x inner join usermigration u on
    x.lastmodifier = u.oldusername
    set x.lastmodifier = u.newusername;

    update links x inner join usermigration u on
    x.creator = u.oldusername
    set x.creator = u.newusername;

    update notifications x inner join usermigration u on
    x.username = u.oldusername
    set x.username = u.newusername;

    update notifications x inner join usermigration u on
    x.lastmodifier = u.oldusername
    set x.lastmodifier = u.newusername;

    update notifications x inner join usermigration u on
    x.creator = u.oldusername
    set x.creator = u.newusername;

    update pagetemplates x inner join usermigration u on
    x.lastmodifier = u.oldusername
    set x.lastmodifier = u.newusername;

    update pagetemplates x inner join usermigration u on
    x.creator = u.oldusername
    set x.creator = u.newusername;

    update spaces x inner join usermigration u on
    x.lastmodifier = u.oldusername
    set x.lastmodifier = u.newusername;

    update spaces x inner join usermigration u on
    x.creator = u.oldusername
    set x.creator = u.newusername;

    update spacepermissions x inner join usermigration u on
    x.permusername = u.oldusername
    set x.permusername = u.newusername;

    update spacepermissions x inner join usermigration u on
    x.lastmodifier = u.oldusername
    set x.lastmodifier = u.newusername;

    update spacepermissions x inner join usermigration u on
    x.creator = u.oldusername
    set x.creator = u.newusername;

    update contentlock x inner join usermigration u on
    x.lastmodifier = u.oldusername
    set x.lastmodifier = u.newusername;

    update contentlock x inner join usermigration u on
    x.creator = u.oldusername
    set x.creator = u.newusername;

    --update emailtemplates x inner join usermigration u on
    --x.lastmodifier = u.oldusername
    --set x.lastmodifier = u.newusername;

    --update emailtemplates x inner join usermigration u on
    --x.creator = u.oldusername
    --set x.creator = u.newusername;

    --update emailtemplates x inner join usermigration u on
    --x.username = u.oldusername
    --set x.username = u.newusername;

    update os_user x inner join usermigration u on
    x.username = u.oldusername
    set x.username = u.newusername;

    update trackbacklinks x inner join usermigration u on
    x.lastmodifier = u.oldusername
    set x.lastmodifier = u.newusername;

    update trackbacklinks x inner join usermigration u on
    x.creator = u.oldusername
    set x.creator = u.newusername;

    Hint:

    • as there can be duplicates in the new username-list, run all SQL-Code except of os_user-update.
    • run only os_user, if there is an error, change the problematic newusername
    • After running the scripts make a full reindexing, to have affect on the recentlyupdated section for example

  3. Sep 25, 2006

    Christian Løverås says:

    Is there a way to do this with the standalone server? Thanks.

    Is there a way to do this with the standalone server? Thanks.

    1. Sep 26, 2006

      Matt Ryall says:

      When Confluence is shut down, the standalone database is stored in a text file c...

      When Confluence is shut down, the standalone database is stored in a text file called confluencedb.log in confluence-home/database. So after shutting down Confluence, you can do a search and replace in this text file to update a username.

      Please make sure you take a backup copy of the database before making any changes, as recommended above. For standalone, this means taking a complete copy of your confluence-home directory.

    2. Nov 17, 2006

      Stephen Morad says:

      I found that I had to modify the confluencedb.script file as well as the conflue...

      I found that I had to modify the confluencedb.script file as well as the confluence-home/database/confluencedb.log file (infact, after making changes in both, it looked like the confluencedb.log file got reset after restarting Confluence, so perhaps the only place requiring the change is confluencedb.script).

  4. Jan 05, 2007

    Brit Pair says:

    I rewrote for SQL Server UPDATE attachments SET attachments.creator = usermi...

    I rewrote for SQL Server

    UPDATE attachments 
SET attachments.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN attachments 
    ON (usermigration.oldusername = attachments.creator)

UPDATE attachments 
SET attachments.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN attachments 
    ON (usermigration.oldusername = attachments.lastmodifier)

UPDATE content 
SET content.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN content 
    ON (usermigration.oldusername = content.creator) 

UPDATE content 
SET content.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN content 
    ON (usermigration.oldusername = content.lastmodifier) 

UPDATE content 
SET content.username = usermigration.newusername
FROM usermigration 
    INNER JOIN content 
    ON (usermigration.oldusername = content.username) 

UPDATE content_label 
SET content_label.owner = usermigration.newusername
FROM usermigration 
    INNER JOIN content_label 
    ON (usermigration.oldusername = content_label.owner) 

UPDATE draft 
SET draft.owner = usermigration.newusername
FROM usermigration 
    INNER JOIN draft 
    ON (usermigration.oldusername = draft.owner) 

UPDATE extrnlnks 
SET extrnlnks.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN extrnlnks 
    ON (usermigration.oldusername = extrnlnks.creator) 

UPDATE extrnlnks 
SET extrnlnks.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN extrnlnks 
    ON (usermigration.oldusername = extrnlnks.lastmodifier) 

UPDATE label 
SET label.owner = usermigration.newusername
FROM usermigration 
    INNER JOIN label 
    ON (usermigration.oldusername = label.owner) 

UPDATE links 
SET links.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN links 
    ON (usermigration.oldusername = links.creator) 

UPDATE links 
SET links.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN links 
    ON (usermigration.oldusername = links.lastmodifier) 

UPDATE notifications 
SET notifications.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN notifications 
    ON (usermigration.oldusername = notifications.creator) 

UPDATE notifications 
SET notifications.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN notifications 
    ON (usermigration.oldusername = notifications.lastmodifier) 

UPDATE notifications 
SET notifications.username = usermigration.newusername
FROM usermigration 
    INNER JOIN notifications 
    ON (usermigration.oldusername = notifications.username) 

UPDATE pagetemplates 
SET pagetemplates.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN pagetemplates 
    ON (usermigration.oldusername = pagetemplates.creator) 

UPDATE pagetemplates 
SET pagetemplates.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN pagetemplates 
    ON (usermigration.oldusername = pagetemplates.lastmodifier) 

UPDATE spaces 
SET spaces.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN spaces 
    ON (usermigration.oldusername = spaces.creator) 

UPDATE spaces 
SET spaces.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN spaces 
    ON (usermigration.oldusername = spaces.lastmodifier) 

UPDATE spacepermissions 
SET spacepermissions.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN spacepermissions 
    ON (usermigration.oldusername = spacepermissions.creator) 

UPDATE spacepermissions 
SET spacepermissions.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN spacepermissions 
    ON (usermigration.oldusername = spacepermissions.lastmodifier) 

UPDATE spacepermissions 
SET spacepermissions.permusername = usermigration.newusername
FROM usermigration 
    INNER JOIN spacepermissions 
    ON (usermigration.oldusername = spacepermissions.permusername) 

UPDATE contentlock 
SET contentlock.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN contentlock 
    ON (usermigration.oldusername = contentlock.creator) 

UPDATE contentlock 
SET contentlock.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN contentlock 
    ON (usermigration.oldusername = contentlock.lastmodifier) 

UPDATE trackbacklinks 
SET trackbacklinks.creator = usermigration.newusername
FROM usermigration 
    INNER JOIN trackbacklinks 
    ON (usermigration.oldusername = trackbacklinks.creator) 

UPDATE trackbacklinks 
SET trackbacklinks.lastmodifier = usermigration.newusername
FROM usermigration 
    INNER JOIN trackbacklinks 
    ON (usermigration.oldusername = trackbacklinks.lastmodifier) 

UPDATE os_user 
SET os_user.username = usermigration.newusername
FROM usermigration 
    INNER JOIN os_user 
    ON (usermigration.oldusername = os_user.username) 

go

  5. Apr 21, 2007

    Martin Cleaver says:

    A mid way point should be to get this added to as a restful service accessible b...

    A mid way point should be to get this added to as a restful service accessible by the SOAP client.


  6. Apr 26, 2007

    Martin Cleaver says:

    #
    1. http://jira.atlassian.com/browse/CONF-4063 is tracking this issue.
    2. I suspect this is a project that I am going to have to tackle. Are there guidelines somewhere that advise how this SQL should become code that runs on the server and invoked from the remote client?

    1. May 03, 2007

      David Chui says:

      I'm afraid you can't execute arbitrary SQL via the remote interface.

      I'm afraid you can't execute arbitrary SQL via the remote interface.

      1. May 07, 2007

        Martin Cleaver says:

        Heh. I didn't want to. I want to have these migration SQL scripts on the server...

        Heh. I didn't want to.

        I want to have these migration SQL scripts on the server and be able to invoke via soap something like:

        java -jar release/confluence-soap-0.4.jar --server http://server1 --password automation --action renameUsers --file users.migration.csv

        This would wrap the table changes in appropriate transactions so no user was partially changed.

        Presently you have to get into the database: it could be error prone and it involves too many different types of people: the administrator, database administrator, and plus possibly a programmer.

        1. May 07, 2007

          David Chui says:

          Hi Martin, I personally do not see how transactions could be partially complete...

          Hi Martin,

          I personally do not see how transactions could be partially completed if you have your DMLs live within a database transaction. In the most simplest case, only do a database commit at the end of the transaction. That should make it atomic. You just need to ensure that your database is ACID compliant.

  7. May 08, 2007

    Frank Stiller says:

    To have only the sqlsolution mentioned is good, as the xmlworkaround was to faul...

    To have only the sql-solution mentioned is good, as the xml-workaround was to faulty, but can you also say a workaround to update bodycontent (like linking to a user profile) which would be broken if you change the username?

    That isnt a showstopper, just to have that in mind. I currently use a sql change to make the necessary field-updates and for the rest i check for profile links in the xml-entities file and change these values directly in the memo-field in the bodycontent. Maybe that could be the work of a xml-rpc tool or the like to change this more automatic. For a sql-replace the memo-field of the bodycontent is to huge, thats the reason why you cant do it like the field updates above.

    1. May 09, 2007

      David Chui says:

      While it is possible to replace text in CLOB

      While it is possible to replace text in CLOB columns in some databases, I find it clumsy and I think your approach of replacing content text in Confluence backups using some regular expressions and then reimport them would be a better approach, for now.

      Perhaps you'd like to comment about that in http://jira.atlassian.com/browse/CONF-4063?

      Regards,
      David

  8. Jun 04, 2007

    Martin Cleaver says:

    None of these instructions work for Oracle. Further, making admins perform datab...

    None of these instructions work for Oracle.

    Further, making admins perform database SQL commands to make up for this functionality is asking for trouble (especially given the many dialects of SQL).

    1. Jun 04, 2007

      Martin Cleaver says:

      Nevertheless here's some instructions for Oracle. YMMV.\\ /update attachments s...

      Nevertheless here's some instructions for Oracle. YMMV.

      /*update attachments
set creator = newusername from usermigration u
where creator = u.oldusername;
*/

update attachments a
set a.creator = (select newusername from usermigration u
where a.creator = u.oldusername);

/*
update attachments
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;
*/

update attachments a
set a.lastmodifier = (select newusername from usermigration u
where a.lastmodifier = u.oldusername);

/*
update content
set lastmodifier = newusername from usermigration u
where lastmodifier = u.oldusername;
*/

update content a
set a.lastmodifier = (select newusername from usermigration u
where a.lastmodifier = u.oldusername);

update content a
set a.creator = (select newusername from usermigration u
where a.creator = u.oldusername);

update content a
set a.username = (select newusername from usermigration u
where a.username = u.oldusername);

update extrnlnks a
set creator = (select newusername from usermigration u
where a.creator = u.oldusername);

update extrnlnks a
set lastmodifier = (select newusername from usermigration u
where a.lastmodifier = u.oldusername);

update links a
set lastmodifier = (select newusername from usermigration u
where a.lastmodifier = u.oldusername);

update links a
set creator = (select newusername from usermigration u
where a.creator = u.oldusername);

update notifications a
set lastmodifier = (select newusername from usermigration u
where a.lastmodifier = u.oldusername);

update notifications a
set creator = (select newusername from usermigration u
where a.creator = u.oldusername);

update pagetemplates a
set lastmodifier = (select newusername from usermigration u
where a.lastmodifier = u.oldusername);

update pagetemplates a
set creator = (select newusername from usermigration u
where a.creator = u.oldusername);

update spaces a
set creator = (select newusername from usermigration u
where a.creator = u.oldusername);

update spaces a
set lastmodifier = (select newusername from usermigration u
where a.lastmodifier = u.oldusername);

update spacepermissions a
set permusername = (select newusername from usermigration u
where a.permusername = u.oldusername);

update spacepermissions a
set creator = (select newusername from usermigration u
where a.creator = u.oldusername);

update spacepermissions a
set lastmodifier = (select newusername from usermigration u
where a.lastmodifier = u.oldusername);

update contentlock a
set creator = (select newusername from usermigration u
where a.creator = u.oldusername);

update contentlock a
set lastmodifier = (select newusername from usermigration u
where a.lastmodifier = u.oldusername);

update os_user a
set username = (select newusername from usermigration u
where a.username = u.oldusername);

update trackbacklinks a
set creator = (select newusername from usermigration u
where a.creator = u.oldusername);

update trackbacklinks a
set lastmodifier = (select newusername from usermigration u
where a.lastmodifier = u.oldusername);

update users a
set name = (select newusername from usermigration u
where a.name = u.oldusername);

/\* Now manually edit the spaces table, replacing spacekey \~oldusername with spacekey \~newusername \*/
/* The following are used in 2.5.2 and probably before. */ 
update content_label a
set owner = (select newusername from usermigration u
where a.owner = u.oldusername);

update content_perm a
set username = (select newusername from usermigration u
where a.username = u.oldusername);

update content_perm a
set creator  = (select newusername from usermigration u
where a.creator = u.oldusername);

update draft a
set owner = (select newusername from usermigration u
where a.owner = u.oldusername);

update label a
set owner = (select newusername from usermigration u
where a.owner = u.oldusername);

update notifications a
set username = (select newusername from usermigration u
where a.username = u.oldusername);

update OS_User a
set username = (select newusername from usermigration u
where a.username = u.oldusername);
 

      This will still leave your Recently Updated list pointing at non-existent personal spaces.

  9. Jul 27, 2007

    André Heie Vik says:

    After changing username, the profile pictures are disabled. The user can see and...

    After changing username, the profile pictures are disabled. The user can see and select the uploaded pictures in Preferences, but they are disabled. How can I correct this in the database? (the pictures are attached to content of type userinfo, but I don't know how this is connected to the user)

    1. Jul 31, 2007

      Fennie Ng says:

      Hi Andre, Could you please raise a support ticket https://support.atlassian.com...

      Hi Andre,

      Could you please raise a support ticket for this issue so that we can follow up from there? Alternatively, you may want to direct your questions to our forum as there might be other user/developers who have already implemented it and should be able to share their ideas with you.

      Thank you.

      Regards,
      Fennie.

  10. Jul 30, 2007

    Ólafur Bragason says:

    Here is an updated version of the update statements in the original article (ite...

    Here is an updated version of the update statements in the original article (items 6, 7 and 8).
    I dumped the Confluence database schema for version 2.5.5 and scanned through it for possible username and spacekey fields and added the missing statements. I am pretty sure I didn't leave anything out.

    UPDATE attachments      SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE attachments      SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE content          SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE content          SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE content          SET username     = newusername FROM usermigration u WHERE username     = u.oldusername;
UPDATE content_label    SET owner        = newusername FROM usermigration u WHERE owner        = u.oldusername;
UPDATE content_perm     SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE content_perm     SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE content_perm     SET username     = newusername FROM usermigration u WHERE username     = u.oldusername;
UPDATE contentlock      SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE contentlock      SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE draft            SET owner        = newusername FROM usermigration u WHERE owner        = u.oldusername;
UPDATE extrnlnks        SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE extrnlnks        SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE label            SET owner        = newusername FROM usermigration u WHERE owner        = u.oldusername;
UPDATE links            SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE links            SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE notifications    SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE notifications    SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE notifications    SET username     = newusername FROM usermigration u WHERE username     = u.oldusername;
UPDATE os_user          SET username     = newusername FROM usermigration u WHERE username     = u.oldusername;
UPDATE pagetemplates    SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE pagetemplates    SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE spacegroups      SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE spacegroups      SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE spacepermissions SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE spacepermissions SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE spacepermissions SET permusername = newusername FROM usermigration u WHERE permusername = u.oldusername;
UPDATE spaces           SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE spaces           SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE trackbacklinks   SET creator      = newusername FROM usermigration u WHERE creator      = u.oldusername;
UPDATE trackbacklinks   SET lastmodifier = newusername FROM usermigration u WHERE lastmodifier = u.oldusername;
UPDATE user             SET name         = newusername from usermigration u WHERE name         = u.oldusername;

UPDATE draft         SET spacekey = '~newusername' WHERE spacekey = '~oldusername';
UPDATE content_label SET spacekey = '~newusername' WHERE spacekey = '~oldusername';
UPDATE decorator     SET spacekey = '~newusername' WHERE spacekey = '~oldusername';

    I left out OS_PROPERTYENTRY as I don't understand it's connection to external_entities (and didn't need to on my server).

    The formatting should make it easier to change for other SQL dialects using global replace.

    1. Aug 02, 2007

      Ólafur Bragason says:

      I found out the hard way that I had missed two fields: UPDATE bandana SET b...

      I found out the hard way that I had missed two fields:

      UPDATE bandana       SET bandanacontext = '~newusername' WHERE bandanacontext = '~oldusername';
UPDATE links         SET destspacekey   = '~newusername' WHERE destspacekey   = '~oldusername';

Add Comment