How to add to table additional column with belonging value

Question

Hi,

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 user_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!

Answer 1

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.

Answer 2

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