Mysql update query with join

Question

I have two tables table_A & table_B fields are

Table_A

id      Name     Code      phone
----------------------------------
 1      ABC       101
 2      BCA       102
 3      ABC       101

Table_B

id       Phone       code
---------------------------
 1        987        101
 2        548        102
 3        887        103

I need to update phone field in Table_A by taking the value from Table_B. I can do this with php loop but this table have huge data. more time needed for this. is there any simple query to update this?

Are you using framework or any ORM libraries that use foreign references? — ggdx, Oct 19 '17 at 15:08
Is there any simple query to update this?: Yes. Lots of examples: here's 1. https://stackoverflow.com/questions/8057565/mysql-update-statement-inner-join-tables — xQbert, Oct 19 '17 at 15:09
and a [demo](http://rextester.com/QVKK33556) using your data/tables: — xQbert, Oct 19 '17 at 15:16

score 2 · Accepted Answer · answered Oct 19 '17 at 15:10

2

you can use an update with join

  update table_A
  inner join table_b on a.code = b.code 
  set a.phone = b.phone

answered Oct 19 '17 at 15:10

ScaisEdge

131,976
10
91
107

score 2 · Answer 2 · answered Oct 19 '17 at 15:13

2

This query will do the trick. Make sure to create a index on column code on both tables for maximal update speed.

UPDATE 
 Table_A
INNER JOIN 
 Table_B
ON
 Table_A.code = Table_B.code 
SET 
 Table_A.phone = Table_B.phone

answered Oct 19 '17 at 15:13

Raymond Nijland

11,488
2
22
34

Mysql update query with join

2 Answers2