In sqlite3: copy a column from table to table

Question

There are several other stackoverflow requests on this topic, but none of them have a satisfactory answer.

I have a BeerReviews table that doesn't have a column (review_text) and another BeerReviewsWithText table that doesn't have another column (brewery name). Otherwise, the rows of the table are ordered the same way, so I would just like to add the brewery_name column from BeerReviews to BeerReviewsWithText.

I run sqlite3 as:

sqlite3 beer_rewiews_with_text.sqlite 

Then I attach a beer review table through:

 attach 'beer_reviews.sqlite' as BR 

I added an empty column to BeerReviewsWithText through:

 alter table BeerReviewsWithText add column beer_brewername varchar; 

A few other questions suggest using insert to populate a column, but this adds new rows to the table, filling only the beer_brewername column.

 insert into BeerReviewsWithText(beer_brewername) select brewery_name from BeerReviews; 

Instead, the update seems to fill in the null values, but when I run the following (similar to the answer to another question ), all the values ​​of the beer_brewername parameter are identical:

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

This seems like weird behavior, as I get the expected list of brewery names at startup:

 select brewery_name from BR.BeerReviews limit 50; 

I'm new to sqlite, so can anyone explain what I'm doing wrong?

Answer 1

When you use a subquery as an expression, for example:

 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 one record, but another record for each record in the external table. This is called a correlated subquery:

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

(It is assumed that you have an ID column as the primary key.)