Using multiple tables in an UPDATE Statement
Here we have two tables (tbl_1 and tbl_2) and we wish to update field_1 of tbl_1
with values from field_2 of tbl_2 where the key columns id_1 and id_2 match.
sqlite> SELECT * FROM tbl_1;
| id_1 | field_1 |
| ---------- | ---------- |
| 99 | |
| 100 | |
| 101 | |
| 102 | |
sqlite> SELECT * FROM tbl_2;
| id_2 | field_2 |
| ---------- | ---------- |
| 100 | alpha |
| 101 | bravo |
| 102 | charlie |
| 103 | delta |
sqlite> UPDATE tbl_1 SET field_1=(SELECT field_2 FROM tbl_2 WHERE id_2 = id_1) ;
sqlite> SELECT * FROM tbl_1;
| id_1 | field_1 |
| ---------- | ---------- |
| 99 | |
| 100 | alpha |
| 101 | bravo |
| 102 | charlie |
sqlite>
UPDATE multiple fields using Values from another table
sqlite> SELECT * FROM tbl_3;
| idnum | city | state |
| ---------- | ----------- | ---------- |
| 100 | Springfield | MA |
| 101 | Washington | DC |
| 102 | Mobile | AL |
sqlite> SELECT * FROM tbl_4;
| idnum | city | state |
| ---------- | ---------- | ---------- |
| 100 | | |
| 101 | | |
| 102 | | |
sqlite> UPDATE tbl_4 SET city=(SELECT tbl_3.city FROM tbl_3 WHERE tbl_4.idnum =tbl_3.idnum),
state = (SELECT tbl_3.state FROM tbl_3 WHERE tbl_4.idnum = tbl_3.idnum);
sqlite> SELECT * FROM tbl_4;
| idnum | city | state |
| ---------- | ----------- | ---------- |
| 100 | Springfield | MA |
| 101 | Washington | DC |
| 102 | Mobile | AL |
sqlite>