SQLite: Update column with value from another tabl

问题:

I have 2 sqlite Table:

table1 (id INTEGER AUTOINCREMENT, status TEXT, name TEXT);

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

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?

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

回答2:

The previous answer will be failed if there is the id column in the table2. It would be better that using the full name table1.id

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