sqlite，从另一个表中的列更新列

本文介绍了sqlite，从另一个表中的列更新列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我目前正在处理一个查询，该查询根据两个表中的城市字段从 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