oracle 使用 INNER Join 更新查询

Update query with INNER Join

提问by santhosha

I have two tables MANUAL_TRANSACTIONS and MANUAL_LIST_TEMP. What I wanted to achieve is to update the MANUAL_TRANSACTIONS with the information from MANUAL_LIST_TEMP. Here only records which is present in the MANUAL_LIST_TEMP table should be updated to MANUAL_TRANSACTIONS.

我有两个表 MANUAL_TRANSACTIONS 和 MANUAL_LIST_TEMP。我想要实现的是使用来自 MANUAL_LIST_TEMP 的信息更新 MANUAL_TRANSACTIONS。此处仅 MANUAL_LIST_TEMP 表中存在的记录应更新为 MANUAL_TRANSACTIONS。

I have done something like below but the problem with following statement is it updates every records from MANUAL_TRANSACTIONS table.

我做了类似下面的事情，但以下语句的问题是它更新了 MANUAL_TRANSACTIONS 表中的每条记录。

UPDATE MANUAL_TRANSACTIONS 
SET ( "Age", "Assigned_To", "Attachments", "Comments", "Completed_Date_Time"
, "Content_Type", "Created", "Created_By","Cycle_Time (Crt to Complete)"
, "Cycle_Time (First reply)", "DISTRIBUTION_CHANNEL")=
        (SELECT MANUAL_LIST_TEMP."Age", MANUAL_LIST_TEMP."Assigned_To", 
        MANUAL_LIST_TEMP."Attachments", MANUAL_LIST_TEMP."Comments", 
        MANUAL_LIST_TEMP."Completed_Date_Time", MANUAL_LIST_TEMP."Content_Type",
        MANUAL_LIST_TEMP."Created", MANUAL_LIST_TEMP."Created_By", 
        MANUAL_LIST_TEMP."Cycle_Time (Crt to Complete)", 
        MANUAL_LIST_TEMP."Cycle_Time (First reply)", 
        MANUAL_LIST_TEMP."Distribution_Channel" 
        FROM MANUAL_LIST_TEMP 
        WHERE MANUAL_LIST_TEMP.ID = MANUAL_TRANSACTIONS.ID)

回答by Danilo Piazzalunga

You should also add the WHERE clause to your UPDATE statement:

您还应该将 WHERE 子句添加到您的 UPDATE 语句中：

UPDATE MANUAL_TRANSACTIONS 
SET ( "Age", "Assigned_To", "Attachments", "Comments", "Completed_Date_Time"
, "Content_Type", "Created", "Created_By","Cycle_Time (Crt to Complete)"
, "Cycle_Time (First reply)", "DISTRIBUTION_CHANNEL")=
        (SELECT MANUAL_LIST_TEMP."Age", MANUAL_LIST_TEMP."Assigned_To", 
        MANUAL_LIST_TEMP."Attachments", MANUAL_LIST_TEMP."Comments", 
        MANUAL_LIST_TEMP."Completed_Date_Time", MANUAL_LIST_TEMP."Content_Type",
        MANUAL_LIST_TEMP."Created", MANUAL_LIST_TEMP."Created_By", 
        MANUAL_LIST_TEMP."Cycle_Time (Crt to Complete)", 
        MANUAL_LIST_TEMP."Cycle_Time (First reply)", 
        MANUAL_LIST_TEMP."Distribution_Channel" 
        FROM MANUAL_LIST_TEMP 
        WHERE MANUAL_LIST_TEMP.ID = MANUAL_TRANSACTIONS.ID)
WHERE MANUAL_TRANSACTIONS.ID IN (SELECT MANUAL_LIST_TEMP.ID FROM MANUAL_LIST_TEMP);

For best results, convert your UPDATE into a MERGE statement:

为获得最佳结果，请将您的 UPDATE 转换为 MERGE 语句：

MERGE INTO MANUAL_TRANSACTIONS tgt
USING (
        SELECT MANUAL_LIST_TEMP.ID,
        MANUAL_LIST_TEMP."Age", MANUAL_LIST_TEMP."Assigned_To", 
        MANUAL_LIST_TEMP."Attachments", MANUAL_LIST_TEMP."Comments", 
        MANUAL_LIST_TEMP."Completed_Date_Time", MANUAL_LIST_TEMP."Content_Type",
        MANUAL_LIST_TEMP."Created", MANUAL_LIST_TEMP."Created_By", 
        MANUAL_LIST_TEMP."Cycle_Time (Crt to Complete)", 
        MANUAL_LIST_TEMP."Cycle_Time (First reply)", 
        MANUAL_LIST_TEMP."Distribution_Channel" 
        FROM MANUAL_LIST_TEMP
) src
ON (tgt.ID = src.ID)
WHEN MATCHED THEN UPDATE
SET tgt."Age" = src."Age"
  , tgt."Assigned_To" = src."Assigned_To"
  [...]

The MERGE statement will only update rows in MANUAL_TRANSACTIONS(the targettable) which have matching rows in MANUAL_LIST_TEMP(the sourcetable).

MERGE 语句只会更新MANUAL_TRANSACTIONS（目标表）中MANUAL_LIST_TEMP（源表）中具有匹配行的行。

回答by Vijay Hulmani

You need one more where clause for UPDATE

您还需要一个用于 UPDATE 的 where 子句

回答by Harshil

Try this

尝试这个

 UPDATE MANUAL_TRANSACTIONS 
    Set Age = MANUAL_LIST_TEMP.AGE, ....
    From MANUAL_TRANSACTIONS 
    inner join MANUAL_LIST_TEMP on MANUAL_LIST_TEMP.ID = MANUAL_TRANSACTIONS.ID

回答by Anurag

UPDATE MANUAL_TRANSACTIONS mt 
  left join MANUAL_LIST_TEMP mlt 
  on mt.ID = mlt.ID set mt.Age=mlt.Age .....

回答by Viraj Dhamal

Please try this one:

请试试这个：

UPDATE MANUAL_TRANSACTIONS mt INNER JOIN MANUAL_LIST_TEMP mlt 
on mt.ID = mlt.ID set mt.Age=mlt.Age,mt.Assigned_To=mlt.Assigned_To, .....;