Mysql double data on query

Question

I have data on 2 tables, like this

Table 1
no|   name  |  address |
1 | alex    |  br st   | 
2 | ujang   |  cilala  |
3 | adu     |  lind st |
4 | ujang   |  bilila  |
5 | ujang   |  gea     |

Table 2
no|   name  |  address |
1 | alex    |          |
2 | ujang   |          |
3 | adu     |          |
4 | adu     |          |

My Query is like this

UPDATE TABLE1 a 
JOIN TABLE2 b ON a.name = b.name
SET a.address = b.address

Result that i want

 Table 2
    no|   name  |  address |
    1 | alex    |  br st   |
    2 | ujang   |  cilala  |
    3 | adu     |  lind st |
    4 | adu     |  lind st |

That all i don't have word to say, i'am freeze.

With regard to duplicates, how do you want to deal with the possibility that there may be more than one matching name in the first table? If you can articulate this to us, we can give you an answer. — Tim Biegeleisen, Jan 07 '16 at 09:55
Do you want to update the address of second table if name match in first table? — shiva chauhan, Jan 07 '16 at 09:58
Maybe you want to check http://www.mysqltutorial.org/mysql-update-join/ for a start. — Vinod Balakrishnan, Jan 07 '16 at 09:58
@SAE If I understand you correctly, you are trying to copy the address from Table 1 to Table2 if the names match? — marklong, Jan 07 '16 at 10:03
What is the logic behind choosing `cilala` as the address for `ujang`? This isn't clear from your question. — Tim Biegeleisen, Jan 07 '16 at 10:03
@marklong yes is that but i have many address and i want only one, from the top. — Sae, Jan 07 '16 at 10:05
@tim my logic is for choosing the very first value from table1 because some of the data have bad address at the second record. but it have field that important, so i can't erase it. — Sae, Jan 07 '16 at 10:08
This is clear, you want the record with the smallest `id` value. — Tim Biegeleisen, Jan 07 '16 at 10:09
Please follow http://stackoverflow.com/questions/15209414/how-to-use-join-in-update-query — vishal vasudeva, Jan 07 '16 at 10:10
for vinod and vishal, thanks. But i'am already see the question and don't find the answer. — Sae, Jan 07 '16 at 10:13

Tim Biegeleisen · Accepted Answer · 2016-01-07T10:39:17.380

2

The following query updates TABLE2 using a temporary table containing only records from TABLE1 with the minimum no value, in cases where a name may occur more than once in TABLE1.

UPDATE TABLE2 a
INNER JOIN
(
    SELECT t1.no, t1.name, t1.address
    FROM TABLE1 t1
    INNER JOIN
    (
        SELECT name, MIN(no) AS no
        FROM TABLE1
        GROUP BY name
    ) t2
    ON t1.name = t2.name AND t1.no = t2.no
) b ON a.name = b.name
SET a.address = b.address

edited Jan 07 '16 at 10:39

answered Jan 07 '16 at 10:08

Tim Biegeleisen

502,043
27
286
360

i have answer from MySQL "Location name in field list is ambiguous", i still try it. – Sae Jan 07 '16 at 10:34
@SAE Sorry, I was missing a table alias. Please try it again. – Tim Biegeleisen Jan 07 '16 at 10:39
1

it have some error but, yes thank you @tim, its works now – Sae Jan 07 '16 at 10:51

score 0 · Answer 2 · answered Jan 07 '16 at 10:06

0

As you mention i think you want all the addresses in table2 from tabel1,so if you want that please change the query with this one :

UPDATE TABLE1 a JOIN TABLE2 b ON a.name = b.name SET b.address = a.address

answered Jan 07 '16 at 10:06

gagan mahatma

336
2
9

score 0 · Answer 3 · answered Jan 07 '16 at 10:09

0

Use like this.

UPDATE table2 
SET 
table2.address = table1.address

FROM table1, table2 
WHERE table1.name = table2.name

answered Jan 07 '16 at 10:09

shiva chauhan

410
2
7

Mysql double data on query

3 Answers3