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

but I am not getting anywhere

Any sugestions anyone ?

Many thanks in advance

Best from Belgium

John

What version of mysql are you using? You almost had it right for a multi table update (verison 4.1 is required IIRC)

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 = ''',
products_fr.product_name,
''' WHERE product_supplier_ref = ''',
products_fr.product_supplier_ref, ''';' )
FROM products_fr

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 ...