使用 SQLite 使用另一个表中的值更新列? [英] Update column with value from another table using SQLite?

问题描述

我有两个 SQLite 表.我想用 table2 中的值更新 table1 中的列.

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

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

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

表2，table2(状态TEXT，名称TEXT，trans_id INTEGER)；:

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

我想将状态和名称从 table2 更新到 table1，其中 table1.id = table2.trans_id.我有这个查询:

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)

它错误地填充了 table1.这是结果表1

It populates table1 wrongly. This is the resultant table1

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

我的要求是:

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

我的查询有什么问题?我怎样才能实现它?

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

推荐答案

我假设 t2.trans_id 是 uniq 或 primary key> 在 table2 中.如果不是，那么如果它返回多个结果，那么更新查询就会爆炸.在这种情况下，您需要使用 WHERE 应用更多过滤器，或者如果需要任何结果，请使用 TOP 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)

这篇关于使用 SQLite 使用另一个表中的值更新列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！