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, but when I run the following (similar to another question's answer) all of the beer_brewername values are identical:

update BeerReviewsWithText set beer_brewername=(select brewery_name from BR.BeerReviews);

This seems like strange behavior since I get the expected list of brewery names when I run:

select brewery_name from BR.BeerReviews limit 50;

I'm new to sqlite, so can someone explain what I'm doing incorrectly?

Answer 1

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.)