Inserting Records from another table using a select query

In the following simple example we have a table called "contact1" which lists names and email addresses. To avoid sending duplicate emails to the same individual we have made the Email address the primary key.

CREATE TABLE contactlist1 (FirstName TEXT,LastName TEXT,Email TEXT NOT NULL PRIMARY KEY);

sqlite> SELECT * FROM contactlist1;

FirstName LastName Email
----------   ----------   -----------------------
Peter Nelson pnelson@oldmail.fake
Alan Reed aj.reed@oldmail.fake

Right now there are only two records in the table but we also have a table called newcontacts from which we can add to our list.

sqlite> SELECT * FROM newcontacts;

FirstName LastName Email
----------   ----------   -----------------------
James Doe james.doe@mymail.fake
Roberta Allen r.allen@mymail.fake
George gpmillford@mymail.fake
Kim Simpson ka.simpson@mymail.fake

Rather than copying the data from the newcontacts table and handkeying it into contactlist1 table we can use a single insert statement with a select query.

INSERT INTO target_table(field1 ,field2 ,field3 ) SELECT field_a, field_b, field_c FROM source_table;

sqlite> INSERT INTO contactlist1 (FirstName,LastName,Email) SELECT FirstName,LastName,Email FROM newcontacts;
sqlite> SELECT * FROM contactlist1;

FirstName LastName Email
----------   ----------  ----------------------
Peter Nelson pnelson@oldmail.fake
Alan Reed aj.reed@oldmail.fake
James Doe james.doe@mymail.fake
Roberta Allen r.allen@mymail.fake
George gpmillford@mymail.fake
Kim Simpson ka.simpson@mymail.fake

You can see that 4 new records were added to the table above. Of course it is not always that easy. The following table specification for contactlist2 is identical to contactlist1 except that LastName and FirstName field may not be null.

CREATE TABLE contactlist2 (FirstName TEXT NOT NULL,LastName TEXT NOT NULL,Email TEXT NOT NULL PRIMARY KEY);

Notice that the third record in the newcontacts table has a NULL value for the LastName field.

sqlite> SELECT * FROM contactlist2;

FirstName LastName Email
----------   ----------  -----------------------
Peter Nelson pnelson@oldmail.fake
Alan Reed aj.reed@oldmail.fake

sqlite> INSERT INTO contactlist2 (FirstName,LastName,Email)SELECT FirstName,LastName,Email FROM newcontacts;
Error: contactlist2.LastName may not be NULL
sqlite> SELECT * FROM contactlist2;

FirstName LastName Email
----------   ----------   ----------------------
Peter Nelson pnelson@oldmail.fake
Alan Reed aj.reed@oldmail.fake

We can correct the record with the NULL value in the newcontacts table and rerun the INSERT statement.

sqlite> UPDATE newcontacts SET LastName="Millford" WHERE Email="gpmillford@mymail.fake";
sqlite> INSERT INTO contactlist2 (FirstName,LastName,Email)SELECT FirstName,LastName,Email FROM newcontacts;
sqlite> SELECT * FROM contactlist2;

FirstName LastName Email
----------   ----------   ----------------------
Peter Nelson pnelson@oldmail.fake
Alan Reed aj.reed@oldmail.fake
James Doe james.doe@mymail.fake
Roberta Allen r.allen@mymail.fake
George Millford gpmillford@mymail.fake
Kim Simpson ka.simpson@mymail.fake
sqlite>

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>

Colin Riley -- Updated June 2010 --