Insert data from one column to another MYSQL based on row

Question

I am new to MYSQL queries so I am struggling with this.

I have two tables

Table 1 

  id       phone1  phone2   name     ...
  1        123      456
  3        234      567
  7        345      678

Table 2

  id        p1        p2    age     ...
  1        1123      2456
  7        1345      2678
  3        1234      2567

ID is the same for both tables. Both tables have many other rows.

I want to copy the data from Table 2 to Table 1 such that the id stays the same.

So the output should be

Table 1 
  id       phone1  phone2   name
  1        1123      2456
  3        1234      2567
  7        1345      2678

Do you want to replace the existing data of `Table 1`? please post the table structures of those two tables (`create table`). Do these two tables structurally similar? — 1000111, Feb 23 '16 at 10:37
Please clarify. Do you want to do this only with mysql query? or on a regular basis with the help of PHP. — Hiren Rathod, Feb 23 '16 at 10:38
The two tables structure is not same, but the columns id phone1 and phone2 are same in both tables. I need to overwrite the table1 values and table2 i will truncate after that. I need this as mysql query as I run this only sometimes like once a day. Thanks — huz_akh, Feb 23 '16 at 10:43
Check my answer here [1]: http://stackoverflow.com/questions/35223907/adding-to-a-database-field-instead-of-overwriting-it-mysql-update-function/35224434#35224434 — Rahul, Feb 23 '16 at 11:08

score 1 · Answer 1 · edited May 23 '17 at 11:59

1

Already answered :

stackoverflow

In your case :

UPDATE table1 t1
    INNER JOIN table2 t2 ON t2.id = t1.id
    SET t1.phone1 = t2.p1,
    t1.phone2 = t2.p2;

edited May 23 '17 at 11:59

Community

1
1

answered Feb 23 '16 at 10:42

ThinkTank

1,187
9
15

This query works. Thanks. Sorry I forgot to mention I dont need to copy any other columns except p1 and p2 from table 2 to phone1 and phone2 to table 1. – huz_akh Feb 23 '16 at 10:51
No problem, just remove them from the query. Dont forget to validate the answer ;) – ThinkTank Feb 23 '16 at 10:53

Vipin Jain · Answer 2 · 2016-02-23T10:56:30.043

1

Try This.

UPDATE table1 tbl1
JOIN table2 tbl2 
ON tbl2.id = tbl1.id
SET tbl1.phone1 = tbl2.p1,
tbl1.phone2 = tbl2.p2;

edited Feb 23 '16 at 10:56

answered Feb 23 '16 at 10:46

Vipin Jain

3,686
16
35

score 1 · Answer 3 · answered Feb 23 '16 at 10:52

1

UPDATE Table1 tab1
    INNER JOIN Table2 tab2 ON tab2.id = tab1.id
    SET 
    tab1.Column2 = tab2.Column3;

Check this sqlfiddle

answered Feb 23 '16 at 10:52

Damodaran

10,882
10
60
81

Insert data from one column to another MYSQL based on row

3 Answers3