MS-Access how to add values to a column in a table from another table in sql

0
votes

I'm trying to add values to a column in one of my tables and the value should be taken from another table that I have in the database. So let's say I have these two tables:

table1                           table2

| ID | in_reason         |             | ID | ...| in_reason |
|  2 |  promotion        |             | 7  | ...| 
|  5 |  replacing someone|             | 5  | ...| 
|  1 |  Hired            |             | 1  | ...| 
                                       | 2  | ...|

Notice here that in table2, my in_reason column is empty and I want it to have the in_reasons from table1 where table2.ID is equal to table1.ID like this:

table2

| ID | ...| in_reason |
| 7  | ...| 
| 5  | ...| replacing someone
| 1  | ...| Hired
| 2  | ...| promotion
1
sqlms-access
Why duplicate data into another table? Build a query that joins on the PK/FK fields to display related data. Assume the ID fields are not both autonumber. - June7
I know what you mean...I had to do it for organization reasons for a colleague to keep all datas in one table rather than a query or multiple queries - tee

1 Answers

1
votes

It should be something like this:

UPDATE Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID 
SET Table2.In_Reason = Table1.IN_Reason;