Give row in other table same values

Question

I'm trying to migrate an existing database to another. The problem is as follows:

I have 2 tables :

1.) hhhhhhhhyhhjhjhhhhhh, rows are :

relatie_id,
tempvar

2.) op_clienten_communicatie, rows are :

Nummer,
Volledige naam,
Volledig adres,
Postcode,
Plaats,
Communicatiemiddel,
Nummer/Adres,
tempvar

When i execute the query ;

UPDATE op_clienten_communicatie, hhhhhhhhyhhjhjhhhhhh
SET op_clienten_communicatie.Nummer = hhhhhhhhyhhjhjhhhhhh.relatie_id
WHERE op_clienten_communicatie.tempvar = hhhhhhhhyhhjhjhhhhhh.tempvar

I only get ALL the op_clienten_communicatie.Nummer fields updated with the same (first entry) from hhhhhhhhyhhjhjhhhhhh.relatie_id

The result I'm looking for is that all the rows in op_clienten_communicatie.Nummer should be updated with the existing values from hhhhhhhhyhhjhjhhhhhh.relatie_id.

Must be a bad day if you name your table `hhhhhhhhyhhjhjhhhhhh` — Kermit, Sep 02 '12 at 17:02
before running the update query, are the nummer and relatie_id fields different (side-by-side) when you run the query `select nummer, relatie_id, h.tempvar from op_clienten_communicatie o inner join hhhhhhhhyhhjhjhhhhhh h on o.tempvar = h.tempvar`? — gangreen, Sep 02 '12 at 17:18
Possible duplicates: [1](http://stackoverflow.com/questions/414828/update-mysql-table-with-data-from-another-table), [2](http://stackoverflow.com/questions/1202075/advanced-mysql-query-update-table-with-info-from-another-table). — Kermit, Sep 02 '12 at 17:19

score 0 · Answer 1 · answered Sep 03 '12 at 00:30

What is the cardinality of tables hhhhhhhhyhhjhjhhhhhh and op_clienten_communicatie? is it one-to-many or many-to-many?

If one to many

UPDATE op_clienten_communicatie a 
           INNER JOIN hhhhhhhhyhhjhjhhhhhh b
              on a.tempvar = b.tempvar
SET a.Nummer = b.relatie_id

But if many-to-many, how would you know which tempvar will be joined?

PS: kindly change the name of the table, kinda sounds weird

Give row in other table same values

1 Answers1