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

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

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

select * from users;

select * from user_details;

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 ;

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 ;

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

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 '%@%' ;

select * from userdetails_new;

alter table users add unique(sid);

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

select * from users join userdetails_new on users.sid = userdetails_new.new_id;