nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

glennrees

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
);

All comments

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"

glennrees

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
);

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

server

confluence-server

atl-adm