CODEWITHSUNDEEP
sqlsqlite
Nidhi
Nidhi

Reputation: 795

How can I update table by using select statement in SQLite

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)

Upvotes: 14

Views: 22444

Answers (5)

Noah
Noah

Reputation: 15340

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

Upvotes: 3

nawfal
nawfal

Reputation: 73253

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.

Upvotes: 5

GarethD
GarethD

Reputation: 69789

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)

Upvotes: 8

Sunil Chavan
Sunil Chavan

Reputation: 524

You Can use like this

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

sqllite do not support join in update query. so this is one of the option

Upvotes: 0

Teja
Teja

Reputation: 13534

For Single Value:

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)

For multiple values

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

Upvotes: 0

Related Questions