In sqlite3: copy a column from one table to another table

Question

There are several other stackoverflow inquiries about this topic, but none of them have a satisfactory answer. I have a table BeerReviews, which is missing a column (review_text), and another table BeerReviewsWithText which is missing a different column (brewery_name). Otherwise the table rows are ordered the same way, so I would like to simply append the brewery_name column from BeerReviews into BeerReviewsWithText. I launch sqlite3 as: ``` sqlite3 beer_rewiews_with_text.sqlite ``` Then I attach the beer reviews table via: ``` attach 'beer_reviews.sqlite' as BR ``` I added an empty column to BeerReviewsWithText via: ``` alter table BeerReviewsWithText add column beer_brewername varchar; ``` Multiple other questions suggest using insert to fill the column, but this appends new rows to the table, populating only the beer_brewername column. ``` insert into BeerReviewsWithText(beer_brewername) select brewery_name from BeerReviews; ``` Instead, an update seems to fill in the null values,…

Accepted Answer

When you use a subquery as an expression, like this:

```
UPDATE BeerReviewsWithText
SET beer_brewername = (SELECT brewery_name
                       FROM BR.BeerReviews)
```

then only the first record returned by the subquery will be used.

You must write a subquery that returns a single record, but a different record for each record in the outer table. This is called a correlated subquery:

```
UPDATE BeerReviewsWithText
SET beer_brewername = (SELECT brewery_name
                       FROM BR.BeerReviews
                       WHERE ID = BeerReviewsWithText.ID)
```

(This assumes that you have an `ID` column as primary key.)

In sqlite3: copy a column from one table to another table

Solution

Problem

Related problems