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"
Best Answer
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
After inserting some test data, the tables contain ...
Problems
When using your original UPDATE, we get "ORA-01732"
When running the same UPDATE, without DISTINCT, we get "ORA-01779"
Let's see if we can do this with a subquery in SET ... (still no joy)
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.
The new "user details" table now contains ...
Now we can also add some constraints, such as:
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.