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
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> 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 -- <- 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.
Best Answer
This will update all rows in the target table that match an email address in the same table that has been marked as 'do not email'