Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    39

    Thumbs up 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

    but I am not getting anywhere

    Any sugestions anyone ?

    Many thanks in advance

    Best from Belgium

    John

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    What version of mysql are you using? You almost had it right for a multi table update (verison 4.1 is required IIRC)

    Code:
    update products_eng, products_fr
    set products_eng.product_name_fr = products_fr.product_name
    where products_eng.product_supplier_ref = products_fr.product_supplier_ref

  3. #3
    Join Date
    Dec 2004
    Location
    Europe
    Posts
    20
    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 ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •