sqlite，从另一个表中的列更新列 [英] sqlite, update column from column in another table

问题描述

我目前正在处理一个查询，该查询根据两个表中的城市字段从 table2.state 中的数据更新 table1.state.换句话说，当 table1 和 table2 中的城市字段匹配时，用 table2 中的状态更新 table1.我遇到的问题是，当没有城市比赛时，会随机加入一个州，例如爱达荷州或加利福尼亚州.有没有办法规定如果表 1 和表 2 中没有城市匹配，则将 'NA' 插入表 1"

table1 table2城邦城邦塔尔萨 塔尔萨 俄克拉荷马州圣地亚哥圣地亚哥加利福尼亚埃克斯茅斯里昂

这是我到目前为止所拥有的，我似乎无法弄清楚 'IF' 语句会在哪里，或者是否有必要.

UPDATE table1SET state = (SELECT state FROM table2 WHERE table2.city = table1.city)WHERE city IN (SELECT city FROM table2 WHERE table2.city = table1.city)

解决方案

从 UPDATE 语句中删除 WHERE 条件.

<块引用>

WHERE city IN (SELECT city FROM table2 WHERE table2.city = table1.city)

SELECT 中的条件适当地处理匹配.

SQLFiddle

I'm currently working on a query that updates table1.state from data in table2.state based on the city field in both tables. To put it another way, When city fields in table1 and table2 match, update table1 with the state from table2. The problem I'm running into is when there isn't a city match, a random state is thrown in, like Idaho or California. Is there a way to dictate "if there is not a city match in table1 and table2 insert 'NA' into table1"

table1                               table2

city         state                   city         state
tulsa                                tulsa        Oklahoma 
san diego                            san diego    California
exmouth
lyon                              

Here's what I have so far, I cant seem to figure out where an 'IF' statement would go or if it's even necessary.

UPDATE table1
           SET state = (SELECT state FROM table2 WHERE table2.city = table1.city)
          WHERE city IN (SELECT city FROM table2 WHERE table2.city = table1.city)

解决方案

Remove the WHERE condition from your UPDATE statement.

WHERE city IN (SELECT city FROM table2 WHERE table2.city = table1.city)

The condition in your SELECT handles the matching appropriately.

SQLFiddle

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