SQLite: How to update column from other table

Question

I have tableA

ID | Zipcode |  State  
1  | 76020   |  NULL  
2  | 40508   |  NULL 
3  | 90040   |  NULL 

and tableB which covers all zip codes and states for the US

Zipcode |  State

00210   |  NH
00211   |  NH
00212   |  NH

How can I update NULL values in tableA from tableB by matching zip codes?

Answer 1

You can use a correlated subquery:

update a
    set state = (select b.state from b where b.zipcode = a.zipcode);

Answer 2

You can use Update with Join as follows -

UPDATE A
SET A.STATE = B.STATE
FROM TABLE A
INNER JOIN TableB B ON A.ZipCode = B.ZipCode;

Answer 3

UPDATE a
SET a.State = b.State
FROM tableA a
    INNER JOIN tableB b ON b.Zipcode = a.Zipcode
WHERE a.State IS NULL