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?

score 11 · Accepted Answer · edited May 21 '23 at 01:00

11

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)

edited May 21 '23 at 01:00

Marcello B.

4,177
11
45
65

answered Feb 23 '15 at 07:42

Mahesh

8,694
2
32
53

1

So essential part here is that we don't need the table which is updated (table1) in the FROM clause. Otherwise, result would be as in the OP (duplicating records in the updated table). – bam Jan 15 '20 at 00:10

score 5 · Answer 2 · answered Dec 04 '17 at 11:40

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

score 0 · Answer 3 · answered May 30 '23 at 11:35

as far as table2.trans_id corresponds to table1.id, (preferable with a foreign key), you may update more efficiently:

update table1
Set status=t2.status, name=t2.name
From (
    Select * from table2
) as t2
where table1.Id=t2.trans_id;

There's a new syntax to update 2 columns at once, by I never used it yet.

Update column with value from another table using SQLite?

3 Answers3

Linked