MySQL - join two tables

Question

Since I can't find any query here that works for me I've decided to ask a question.

I have table1 which has the next columns:

id | name | address | other_id
-------------------------------
1  | john | blvd 123| null

I have table2 which has the next columns:

id | other_id
--------------
1  | 20301

I would like to update table1.other_id with the table2.other_id according to the id.

What's the syntax for it ?

Thanks.

Answer 1

try this:

UPDATE table1 JOIN table2  ON table1.id = table2.id set table1.other_id=table2.other_id

Answer 2

UPDATE table1 JOIN table2 
ON tabel1.id = table2.id
SET table1.other_id=table2.other_id;

Answer 3

Use this query

UPDATE table1 u
INNER JOIN table2 s on
    u.other_id= s.other_id
SET u.other_id= s.other_id

Answer 4

Firstly, table1 should point table1.other_id as foreign key mapped to table2.other_id. For insert you can use the following statement:

INSERT INTO table1(other_id) SELECT table2.other_id FROM table2 WHERE table2.other_id=1;