Enzo Enzo - 29 days ago 5
MySQL Question

MySQL UPDATE from same table and SELECT

I have this table ps_country_lang with country translations, that are not properly translated. I would need to perform an UPDATE, to replace the content of name where id_lang equals 3 with the contents of name where id_lang equals 2 from the same id_country.

This is a small part of the table, so you can understand better:

+-----------+-----------+---------------+
|id_country |id_lang |name |
+-----------+-----------+---------------+
|27 |1 |Nueva Zelanda |
|27 |2 |New Zealand |
|27 |3 |Nueva Zelanda |
|28 |1 |Corea del Sur |
|28 |2 |South Korea |
|28 |3 |Corea del Sur |
|29 |1 |Israel |
|29 |2 |Israel |
|29 |3 |Israel |
+-----------+-----------+---------------+

Answer
update ps_country_lang dst
join ps_country_lang src on src.id_country = dst.id_country 
                        and src.id_lang = 2 
                        and dst.id_lang = 3
set dst.name = src.name