Update column with another table's value

Question

I have two tables, users (with columns email, name, id) and user_details (with columns username, address, details). Currently the username in user_details is populated with a mix of ID's and Email's. I want the table to have all ID's. Is there a way I can match the rows in user details that are currently populated with email to the row with the same email in the users table, and update it with the id of that row? I have tried the below query:

update 
(select distinct
    u.sid as new_id,
    ud.username as id
from user_details ud
    inner join users u on
        lower(ud.username) = u.email) up set up.id = up.new_id;

to try and join the two tables and then update the join, but I am getting the following error:

SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table 01779. 00000 - "cannot modify a column which maps to a non key-preserved table"

Answer 1

When trying to create a situation that resembles the one you are describing, I have used the following DDL code (notice that there are no constraints), using Oracle 12c:

Test setup

create table users (
  email varchar2(32)
, name varchar2(32)
, sid varchar2(32) -- <- assumption: data type NOT number
);

create table user_details (
  username varchar2(32)  -- <- populated with a mix of ids and emails
, address varchar2(64)
, details varchar2(64)
); 

After inserting some test data, the tables contain ...

SQL> select * from users;
EMAIL                  NAME    SID  
[email protected]  name_1  1    
[email protected]  name_2  2    
[email protected]  name_3  3  

-- user_details: username contains "a mix of ID's and Email's"
SQL> select * from user_details;
USERNAME               ADDRESS    DETAILS                     
1                      address_1  details_1                   
[email protected]             more details (user 1)       
2                      address_2  details_2                   
[email protected]             more details (user 2)       
3                      address_3  details_3                   
[email protected]             more details (user 3)       
3                      ---        some more details (user 3)  

Problems

When using your original UPDATE, we get "ORA-01732"

update 
(select distinct
    u.sid as new_id,
    ud.username as id
from user_details ud
    inner join users u on
        lower(ud.username) = u.email) up set up.id = up.new_id ;
Error at Command Line : 2 Column : 1
Error report -
SQL Error: ORA-01732: data manipulation operation not legal on this view
01732. 00000 -  "data manipulation operation not legal on this view"

When running the same UPDATE, without DISTINCT, we get "ORA-01779"

update ( 
  select 
    u.sid as new_id,
    ud.username as id
  from user_details ud
    inner join users u on lower(ud.username) = u.email
) up 
set up.id = up.new_id ;
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

Let's see if we can do this with a subquery in SET ... (still no joy)

UPDATE user_details 
SET user_details.username = 
(
    SELECT DISTINCT users.sid AS new_id
    FROM users, user_details
    WHERE LOWER(user_details.username) = users.email
);
Error report -
ORA-01427: single-row subquery returns more than one row

Solution

Maybe you want to consider the following solution: leave the original user_details table as it is. Create a new table by SELECTing everything you need from user_details.

create table userdetails_new
as
select 
  u.sid       as new_id -- <- sid corresponds to an email address in users
, ud.address  as address
, ud.details  as details
from user_details ud
  join users u on lower(ud.username) = u.email
union
select
  username -- <- username that is NOT an email address
, address
, details
from user_details
where username not like '%@%' ;

The new "user details" table now contains ...

SQL> select * from userdetails_new;
NEW_ID  ADDRESS    DETAILS                     
1                  more details (user 1)       
1       address_1  details_1                   
2                  more details (user 2)       
2       address_2  details_2                   
3                  more details (user 3)       
3                  some more details (user 3)  
3       address_3  details_3  

Now we can also add some constraints, such as:

alter table users add unique(sid);

alter table userdetails_new
add constraint fkey_userdetails
foreign key (new_id) references users (sid) ;

I'm sure that there are other solutions for this. However, the suggested procedure allows us to keep the original "user_details" data as long as we want ie until we have checked that the update/transformation was successful and correct. Dbfiddle here.

Answer 2

You need to tell it to UPDATE a table but you are currently telling it to UPDATE a query

UPDATE user_details 
SET user_details.id = 
(
    SELECT DISTINCT users.sid AS new_id
    FROM users 
    WHERE LOWER(user_details.username) = users.email)
);