2

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"

2

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.

1

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)
);
  • If there is more than one users.sid, we'll get a "ORA-01427: single-row subquery returns more than one row." (Although: at the first glance I thought your UPDATE should do the job :-)) – stefan Sep 16 '17 at 17:03
  • When there is more than 1 then how are you determining which one you want? – indiri Sep 16 '17 at 17:04
  • dbfiddle.uk/… – stefan Sep 16 '17 at 17:15
  • If you take UNIQUE out of users in the dbfiddle? So you mean that multiple users may have the same email address. In that case (more than one user has that email) are you intending to just pick whichever one is first? Or do you not want to do the update if there is more than 1? – indiri Sep 16 '17 at 17:35
  • Also your dbfiddle does not seem to match the original question. The original question said that username and email are mixed in the same column is user_details but in fiddle they are separate columns. Also in the original you were updating user_details.username=users.id but now you want to use a new column and put the user_details.user_id=users.id. The attached fiddle does not have any situations where there would be more than one users.id that matches. – indiri Sep 16 '17 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.