Update column with another table's value
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) -- &lt;- assumption: data type NOT number
);

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


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

SQL&gt; select * from users;
EMAIL                  NAME    SID  
user.mail1@domain.org  name_1  1    
user.mail2@domain.org  name_2  2    
user.mail3@domain.org  name_3  3  

-- user_details: username contains "a mix of ID's and Email's"
SQL&gt; select * from user_details;
USERNAME               ADDRESS    DETAILS                     
1                      address_1  details_1                   
user.mail1@domain.org             more details (user 1)       
2                      address_2  details_2                   
user.mail2@domain.org             more details (user 2)       
3                      address_3  details_3                   
user.mail3@domain.org             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 -- &lt;- 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 -- &lt;- username that is NOT an email address
, address
, details
from user_details
where username not like '%@%' ;


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

SQL&gt; 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.