Unanswered: From one table to the other - this should not be difficult ...
Happy New Year !
I have two tables: "products_eng" (in english) and "products_fr" (in french)
They are identical except that "products_eng" has one extra field "product_name_fr" (read on, it makes sense)
I want to transfer the content of the field "product_name" of the french table into the field "product_name_fr" in the english table (so I no longer need the french table - see? I told you it would make sense
Every row is identified by a field "product_supplier_ref"
I have tried various models of "update from" like
update products_eng from products_fr
set products_eng.product_name_fr = products_fr.product_name
where products_eng.product_supplier_ref = products_fr.product_supplier_ref
If you're not on V4 and can't therefore use multi-table update, the following approach might be helpful:
SELECT CONCAT( ' UPDATE products_eng SET ',
' product_name_fr = ''',
''' WHERE product_supplier_ref = ''',
products_fr.product_supplier_ref, ''';' )
will yield something (perhaps) like:
UPDATE products_eng SET product_name_fr = 'Bon jour' WHERE product_supplier_ref = '1234';
UPDATE products_eng SET product_name_fr = 'Bonne Nuit' WHERE product_supplier_ref = '3456';
UPDATE products_eng SET product_name_fr = 'Champagne' WHERE product_supplier_ref = '1457';
UPDATE products_eng SET product_name_fr = 'Pernod' WHERE product_supplier_ref = '5678';
UPDATE products_eng SET product_name_fr = 'Vin Rouge' WHERE product_supplier_ref = '0034';
Grab the output und run it through mysql client again ...
It is clumsy and looks weird - the idea behind it is to "generate" a bunch of update-statements against a single table. Might also be long-running if the table is big - but then , better than typing it all in
Of, course, product_supplier_ref should be some kind of "primary key"
so you're updates will go to the right records - but you can check the
update-statements before executing them.
BEWARE if there's apostrophes in the product text - this will probably
give syntax error on update and will have to be handled separately.
P.S.: I didn't test the statement above, there may be smaller faults like missing apostrophes etc. I'd recommend to put this in an SQL-syntax.highlighting tool like phpmyadmin to find any errors ...