用另一個表的值更新列

我有兩個表，users（帶有列email，，name）id和user_details（帶有列username，，，address）details。目前，使用者名中的使用者名user_details混合了 ID 和電子郵件。我希望該表具有所有 ID。有沒有一種方法可以將目前填充有電子郵件的使用者詳細資訊中的行與使用者表中具有相同電子郵件的行匹配，並使用該行的 id 更新它？我嘗試了以下查詢：

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;

嘗試加入這兩個表，然後更新加入，但我收到以下錯誤：

SQL 錯誤：ORA-01779：無法修改映射到非鍵保留表 01779 的列。00000 -“無法修改映射到非鍵保留表的列”

在嘗試創建類似於您所描述的情況時，我使用了以下 DDL 程式碼（注意沒有約束），使用 Oracle 12c：

測試設置

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

插入一些測試數據後，表格包含…

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)  

問題

使用原始更新時，我們得到“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"

當執行相同的 UPDATE 時，沒有 DISTINCT，我們得到“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.

讓我們看看我們是否可以用 SET 中的子查詢來做到這一點……（仍然沒有樂趣）

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

解決方案

也許您想考慮以下解決方案：保持原來的 user_details 表不變。通過從 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 '%@%' ;

新的“使用者詳細資訊”表現在包含…

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  

現在我們還可以添加一些約束，例如：

alter table users add unique(sid);

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

我確信還有其他解決方案。但是，建議的過程允許我們保留原始的“user_details”數據，只要我們想要，即直到我們檢查更新/轉換是否成功且正確。 Dbfiddle在這裡。

您需要告訴它更新表，但您目前告訴它更新查詢

UPDATE user_details 
SET user_details.id = 
(
   SELECT DISTINCT users.sid AS new_id
   FROM users 
   WHERE LOWER(user_details.username) = users.email)
);

引用自：https://dba.stackexchange.com/questions/186014