How can I update table by using select statement in SQLite

Question

I want to update one table with the help of the foreign key of others table.

I am trying to do something like this:

UPDATE tbl_1 
SET field1 = 6, field12 = NULL 
WHERE field3 = (SELECT tbl_2.item1 
                FROM tbl_1, tbl_2 
                WHERE tbl_1.field3 = tbl_2.item1 AND tbl_2.item2 = 135)

OR

UPDATE tbl_1 
SET field1 = 6, field12 = NULL 
WHERE field3 = (SELECT item1 FROM tbl_2 WHERE item2 = 135)

Answer 1

I think either of the following will work:

UPDATE  tbl_1 
SET     field1 = 6, field12 = NULL 
WHERE   EXISTS 
        (   SELECT 1
            FROM tbl_2 
            WHERE tbl_1.field3 = tbl_2.item1 
            AND tbl_2.item2 = 135
        )

OR

UPDATE  tbl_1 
SET     field1 = 6, field12 = NULL 
WHERE   field3 IN (SELECT item1 FROM tbl_2 WHERE item2 = 135)

Answer 2

This is because the SELECT returns more than one row. Do this instead:

UPDATE tbl_1 SET field1 = 6, field12 = NULL 
WHERE field3 IN (SELECT item1 FROM tbl_2 WHERE item2 = 135)

When SELECT returns a table (or multiple rows) IN is used. If you are sure the inner query should return only one row, then you will have to adjust the inner query accordingly. Like this or so:

UPDATE tbl_1 SET field1 = 6, field12 = NULL 
WHERE field3 = (SELECT item1 FROM tbl_2 WHERE item2 = 135 ORDER BY myValue LIMIT 1)

Its safer to use IN here 'cos it can handle both single record and multiple records returned from the SELECT statement.

Answer 3

You can also use an INSERT OR REPLACE statement, something like the following:

Assume tbl_1 has 4 columns: key, field1, field2, field3
and you want to update field2 with the matching value from tbl_2

INSERT OR REPLACE INTO tbl_1
SELECT tbl_1.key, tbl_1.field1, tbl_2.value, tbl_1.field3
FROM tbl_1 JOIN tbl_2 ON tbl_2.key = tbl_1.key