6

How can I translate this query to 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

This is what I have tried.

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

The query works but I get wrong results.

10

In order to implement the join on two columns simultaneously, you can use an EXISTS predicate:

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

If you are using version 3.15.0 or later, you can also take advantage of row value comparisons:

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);
  • 1
    Also: WHERE NOT EXISTS (SELECT Field1, Field2 EXCEPT SELECT Field1, Field2 FROM Table_2) ; (haven't tested it, not sure it works in SQLite) (or EXISTS - INTERSECT) – ypercubeᵀᴹ May 10 '18 at 12:16

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.