Update column with value from another table using SQLite?

Question

I have two SQLite tables. I want to update a column in table1 with a value from table2.

Table 1, table1 (id INTEGER AUTOINCREMENT, status TEXT, name TEXT);:

| id |  status   | name |
|----|-----------|------|
|  1 | pending   | xyz  |
|  2 | completed | abc  |

Table 2, table2 (status TEXT, name TEXT, trans_id INTEGER);:

| trans_id |  status   | name |
|----------|-----------|------|
|        1 | refunded  | cvb  |
|        2 | cancelled | asd  |

I want to update status and name from table2 to table1 where table1.id = table2.trans_id. I have this query:

UPDATE table1 
SET status = (SELECT t2.status FROM table1 t1,table2 t2 WHERE t1.id = t2.trans_id) , 
name = (SELECT t2.name FROM table1 t1,table2 t2 WHERE t1.id = t2.trans_id)
WHERE id IN (SELECT trans_id FROM table1 t1,table2 t2 WHERE t1.id = t2.trans_id)

It populates table1 wrongly. This is the resultant table1

| id |  status  | name |
|----|----------|------|
|  1 | refunded | cvb  |
|  2 | refunded | cvb  |

My requirement is this:

| id |  status   | name |
|----|-----------|------|
|  1 | refunded  | cvb  |
|  2 | cancelled | asd  |

Whats wrong with my query? How can I achieve it?

Answer 1

I am assuming that the t2.trans_id is uniq or primary key in table2. If not then if it return multiple result then the update query will blow up. In that case either you need to apply the more filter using the WHERE or use the TOP 1 if any result will be needed.

           UPDATE table1 
           SET status = (SELECT t2.status FROM table2 t2 WHERE t2.trans_id = id) , 
               name = (SELECT t2.name FROM table2 t2 WHERE t2.trans_id = id)
           WHERE id IN (SELECT trans_id FROM table2 t2 WHERE t2.trans_id= id)