Help with merging user content ownership

[glennrees]

Hi,

 I am trying to follow How to merge two users' content ownership and permissions in Confluence where I am migrating one confluence site to a new site. I am migrating content and there will be a new authentication source so old user names will change. The users want old user names mapped to new user names.

Everything goes ok in the reference document until I get to the section:

Update the username associated to the old_user_key to the new username:

UPDATE user_mapping SET username = newusername, lower_username = newusername FROM tmp_usermigration u WHERE username = u.oldusername;

 When I run this SQL I get an error:

"SQL command not ended properly" at the word FROM.

This online syntax checker finds the same issue if you plug in that code:

EverSQL SQL Query Syntax Check & Validator

I am using an Oracle database. The article is written for PostgreSQL, but I can't see what the issue is here as this SQL should work for both I think. Any Oracle/SQL gurus able to help?

Thanks,

Glenn

[glennrees · Accepted solution]

I have worked it out. I needed to add some more select statements to be more explicit with what I am trying to set. All the other SQL statements in the mentioned document have the same problem, and I think should be updated. This works:

UPDATE
  user_mapping
SET
  username = (
SELECT
  u.newusername
FROM
  tmp_usermigration u
),
  lower_username = (
SELECT
  u.newusername
FROM
  tmp_usermigration u
)
WHERE
  username = (
    SELECT
      u.oldusername
    FROM
      tmp_usermigration u
);

[Stephen Sifers]

Hello Glenn,

From reviewing your notes and steps you took and the error you provided (thank you for providing the error!), I believe the Oracle DB wants a different syntax for the state. The syntax should look similar to as follow (please ensure you test this on a staging instance before testing in production).

UPDATE
    user_mapping
SET
    username = newusername,
    lower_username = newusername
WHERE
    username = (
      SELECT
          oldusername
      FROM
          tmp_usermigration
)

Please give this query a try and let us know the results.

Regards,
Stephen Sifers

[glennrees]

Hi,

 Thanks Stephen, I have tried that out but I had an error "NEWUSERNAME: invalid identifier". I got around that with using single quotes around the values like this:

UPDATE
    user_mapping
SET
    username = 'newusername',
    lower_username = 'newusername'
WHERE
    username = (
      SELECT
          oldusername
      FROM
          tmp_usermigration
);

I now get a row updated. I have one test user I am trying this on. However rather than  username and lower_username being replaced with the new users name from the tmp table, they are replaced with the literal word "newusername".

Any thoughts?

Glenn

[Stephen Sifers]

Hello Glenn,

The NEWUSERNAME item is meant to be a variable you're setting the username too. With this said, the query should look similar to the following (If you're wanting to keep the old username as the new):

UPDATE
  user_mapping
SET
  username = u.oldusername,
  lower_username = u.oldusername
WHERE
  username = (
    SELECT
      oldusername
    FROM
      tmp_usermigration u
);

Please give this a try within your staging instance to ensure it works as expected.

Regards,
Stephen Sifers

[glennrees]

Hi Stephen,

 Thanks, but I am trying to swap the old username for the new.

I have tried this to achieve this based on what you have above

UPDATE
  user_mapping
SET
  username = u.newusername,
  lower_username = u.newusername
WHERE
  username = (
    SELECT
      oldusername
    FROM
      tmp_usermigration u
);

But I get  the error "u.username: invalid identifier"

I get the same with u.oldusername.

I think this is because the first select statement does not know where the u table is as we have no FROM statement. If I add the FROM statement to the first select, we get the original error again:

"SQL command not ended properly"