CODEWITHSUNDEEP
sqlite
grigs
grigs

Reputation: 1150

sqlite, update column from column in another table

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)

Upvotes: 2

Views: 3674

Answers (1)

Brien Foss
Brien Foss

Reputation: 3367

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

Upvotes: 3

Related Questions