clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799373 fiddles created (41680 in the last week).

create table users ( email varchar2(32) , name varchar2(32) , sid varchar2(32) );
 hidden batch(es)


create table user_details ( username varchar2(32) , address varchar2(64) , details varchar2(64) );
 hidden batch(es)


begin insert into users (email, name, sid) values ('user.mail1@domain.org','name_1','1'); insert into users (email, name, sid) values ('user.mail2@domain.org','name_2','2'); insert into users (email, name, sid) values ('user.mail3@domain.org','name_3','3'); -- user_details: username contains "a mix of ID's and Email's" insert into user_details (username, address, details) values ('1','address_1','details_1'); insert into user_details (username, address, details) values ('user.mail1@domain.org',null,'more details (user 1)'); insert into user_details (username, address, details) values ('2','address_2','details_2'); insert into user_details (username, address, details) values ('user.mail2@domain.org',null,'more details (user 2)'); insert into user_details (username, address, details) values ('3','address_3','details_3'); insert into user_details (username, address, details) values ('user.mail3@domain.org',null,'more details (user 3)'); insert into user_details (username, address, details) values ('3',null ,'some more details (user 3)'); commit; end; /
1 rows affected
 hidden batch(es)


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
 hidden batch(es)


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)
 hidden batch(es)


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 ;
ORA-01732: data manipulation operation not legal on this view
 hidden batch(es)


update ( select u.sid as new_id, ud.username as id from user_details ud join users u on lower(ud.username) = u.email ) up set up.id = up.new_id ;
ORA-01779: cannot modify a column which maps to a non key-preserved table
 hidden batch(es)


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 );
ORA-01427: single-row subquery returns more than one row
 hidden batch(es)


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 '%@%' ;
7 rows affected
 hidden batch(es)


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


alter table users add unique(sid);
 hidden batch(es)


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


select * from users join userdetails_new on users.sid = userdetails_new.new_id;
EMAIL NAME SID NEW_ID ADDRESS DETAILS
user.mail1@domain.org name_1 1 1 address_1 details_1
user.mail1@domain.org name_1 1 1 more details (user 1)
user.mail2@domain.org name_2 2 2 address_2 details_2
user.mail2@domain.org name_2 2 2 more details (user 2)
user.mail3@domain.org name_3 3 3 address_3 details_3
user.mail3@domain.org name_3 3 3 more details (user 3)
user.mail3@domain.org name_3 3 3 some more details (user 3)
 hidden batch(es)