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

there several other stackoverflow inquiries topic, none of them have satisfactory answer.

i have table beerreviews, missing column (review_text), , table beerreviewswithtext missing different column (brewery_name). otherwise table rows ordered same way, append brewery_name column beerreviews beerreviewswithtext.

i launch sqlite3 as:

sqlite3 beer_rewiews_with_text.sqlite 

then attach beer reviews table via:

attach 'beer_reviews.sqlite' br 

i added empty column beerreviewswithtext via:

alter table beerreviewswithtext add column beer_brewername varchar; 

multiple other questions suggest using insert fill column, appends new rows table, populating beer_brewername column.

insert beerreviewswithtext(beer_brewername) select brewery_name beerreviews; 

instead, update seems fill in null values, when run following (similar another question's answer) of beer_brewername values identical:

update beerreviewswithtext set beer_brewername=(select brewery_name br.beerreviews); 

this seems strange behavior since expected list of brewery names when run:

select brewery_name br.beerreviews limit 50; 

i'm new sqlite, can explain i'm doing incorrectly?

when use subquery expression, this:

update beerreviewswithtext set beer_brewername = (select brewery_name                        br.beerreviews) 

then first record returned subquery used.

you must write subquery returns single record, different record each record in outer table. called correlated subquery:

update beerreviewswithtext set beer_brewername = (select brewery_name                        br.beerreviews                        id = beerreviewswithtext.id) 

(this assumes have id column primary key.)