Update column from another MYSQL table excluding already existing data

Question

I have 2 tables as described below...

table_1
| id | system_id |
| 1  | 3         |
| 2  | 12        |
| 3  | 45        |
| 4  | 7         |

table_2
| id | system_id |
| 1  | 3         |
| 2  | 12        |

I'd like to update table_2 with the remaining rows from table_1, excluding the already existing rows.

I tried the following query...

UPDATE table_2
INNER JOIN table_2 ON table_2.system_id = table_1.system_id
SET table_2.system_id = table_1.system_id

This updates the table_2 with all rows from table_1. I'm unable to work out how to exclude the existing rows. Thanks for any help in advance.

Answer 1

If you want to create new rows in table_2, then you need insert, not update. If you want to update system_id on existing rows at the same time, you can use on duplicate key.

Assuming that the primary key of each table is id, you would do:

insert into table_2 (id, system_id)
select id, system_id 
from table_1
on duplicate key update system_id = values(system_id)