How to add to table additional column with belonging value

The table aggregate_monthly_conversations consists of columns user_A, user_B, user_B_location and the table monthly_statistics consists only of user_A AND user_B

I would like to add the column user_B_location to table monthly_statistics and fill it with appropriate values.

To get appropriate values user_B_location for u开发者_Go百科ser_B in table monthly_statistics I can run the following query:

SELECT t1.user_B_location 
FROM aggregate_monthly_conversations AS t1  
INNER JOIN monthly_statistics AS t2 ON t1.user_B = t2.user_B

Anyway I don't know how to add additional column to monthly_statistics and fill it with values returned by the query above. I would appreciate if anyone could help to compose the query that solves this quetsion.

Thank you!

You need to add the new column first. After you add it, then you can update it with the desired values.

Step 1

alter table monthly_statistics
    add user_B_location int /* or whatever datatype is appropriate */

Step 2

update ms
    set user_B_location = amc.user_B_location
    from monthly_statistics ms
        inner join aggregate_monthly_conversations amc
            on ms.user_B = amc.user_B

You'll need some kind of relationship between the two tables, then just write an Update statement to update all of the values for your new column.