如何在兩個欄位上使用 INNER JOIN 更新 SQLITE 列

如何將此查詢轉換為 SQLite：

UPDATE Table_1 
INNER JOIN Table_2 ON (Table_1.Field1 = Table_2.Field1) 
AND(Table_1.Field2 = Table_2.Field2)
SET Table_1.Field3 = Table_2.Field3

這是我嘗試過的。

UPDATE Table_1 
SET Field3 = (SELECT Field3
           FROM Table2
           WHERE (Field1 = Table_2.Field1)
           AND (Field2 = Table_2.Field2))
WHERE Field1 IN (SELECT Table_2.Field1
           FROM Table2
           WHERE (Field1 = Table_2.Field1)
           AND (Field2 = Table_2.Field2)) 
AND Field2 IN (SELECT Table_2.Field2
           FROM Table2
           WHERE (Field1 = Table_2.Field1)
           AND (Field2 = Table_2.Field2)) 

查詢有效，但我得到錯誤的結果。

為了同時在兩列上實現連接，可以使用 EXISTS 謂詞：

UPDATE Table_1 
SET Field3 = (SELECT Field3
             FROM Table_2
             WHERE (Table_1.Field1 = Table_2.Field1)
               AND (Table_1.Field2 = Table_2.Field2))
WHERE EXISTS (SELECT *
             FROM Table_2
             WHERE (Table_1.Field1 = Table_2.Field1)
               AND (Table_1.Field2 = Table_2.Field2));

如果您使用的是3.15.0或更高版本，您還可以利用行值比較：

UPDATE Table_1 
SET Field3 =
   (SELECT Field3
    FROM Table_2
    WHERE (Table_1.Field1, Table_1.Field2) = (Table_2.Field1, Table_2.Field2))
WHERE (Field1, Field2) IN (SELECT Field1, Field2 FROM Table_2);

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