#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2010
    Posts
    2
    Rep Power
    0

    Smile Replace part of a text field with text stored in another table


    Hi all,

    Have a little prob to solve with a mysql database that I want to be able to fix by running an sql query. I want to change just part of a string stored in 1 table with the correct value for that product and that value is stored in another table at present.

    Table1 name: wp_wpsc_product_files

    Fields: ID, Name, Description, Additional_Description

    Table2 name: wp_wpsc_productmeta

    Fields: ID, Product_ID, Meta_key, Meta_Value

    The Product_ID in the second table – is the link between the ID field in the wp_wpsc_product_files table

    The table wp_wpsc_productmeta where meta_key = SKU has the info I need in the meta_value field for each
    Product in the wp_wpsc_product_files table

    In the additional_description field (its a long text field type) there is a string of characters within a lot of other info – the string is: HDKLEPTO
    The HDKLEPTO string is in the additional_description field for every product in the product_files table.
    I need to replace this HDKLEPTO string with the products correct info from the productmeta table which has the correct info for each product
    In the meta_value field where the meta_key=SKU

    Pseudo-code::

    Update wp_wpsc_product_files
    Set additional_description
    Replace within additional_description HDKLEPTO
    join wp_wpsc_product_files and
    Wp_wpsc_productmeta using product_id and ID field
    get meta_value for each product where meta_key = “SKU”

    Hope this makes sense
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    225
    Rep Power
    11
    This makes sense

    Code:
    CREATE TABLE wp_wpsc_product_files(
      ID INT PRIMARY KEY,
      Name VARCHAR(255),
      Description VARCHAR(255),
      Additional_Description LONGTEXT
    );
    
    INSERT INTO wp_wpsc_product_files VALUES (1, 'product1', 'description1', 'some text and HDKLEPTO'),
      (2, 'product2', 'description2', 'some text and HDKLEPTO'),
      (3, 'product3', 'description3', 'some text and HDKLEPTO');
    
    CREATE TABLE wp_wpsc_productmeta(
      ID INT PRIMARY KEY,
      Product_ID INT,
      Meta_key VARCHAR(255),
      Meta_Value VARCHAR(255)
    );
    
    INSERT INTO wp_wpsc_productmeta VALUES (1, 1, 'SKU', 'value1'),
      (2, 1, 'SKU', 'value2'),
      (3, 1, 'notSKU', 'value3'),
      (4, 2, 'SKU', 'value4'),
      (5, 3, 'notSKU', 'value5'),
      (6, 3, 'SKU', 'value6'),
      (7, 3, 'SKU', 'value7');
    And our update command is -
    Code:
    UPDATE
      wp_wpsc_product_files f,
      (SELECT
        product_id,
        GROUP_CONCAT(meta_value) meta_value
      FROM
        wp_wpsc_productmeta
      WHERE
        meta_key = 'SKU'
      GROUP BY
        product_id) m
    SET
      f.Additional_Description = REPLACE(f.Additional_Description, 'HDKLEPTO', m.meta_value)
    WHERE
      f.ID = m.product_id;
    Get results -
    Code:
    SELECT ID, Name, Description, Additional_Description FROM wp_wpsc_product_files;
    +----+----------+--------------+-----------------------------+
    | ID | Name     | Description  | Additional_Description      |
    +----+----------+--------------+-----------------------------+
    |  1 | product1 | description1 | some text and value1,value2 |
    |  2 | product2 | description2 | some text and value4        |
    |  3 | product3 | description3 | some text and value6,value7 |
    +----+----------+--------------+-----------------------------+
    Last edited by Devart Team; June 25th, 2010 at 01:45 AM.
    Devart Company,
    MySQL management tools
    http://www.devart.com/dbforge/mysql/
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2010
    Posts
    2
    Rep Power
    0

    Thanks


    Hi Devart Team,

    Thanks for your answer...though didn't make sense to me However, after waking up afresh, this is what I ended up doing to solve, which although required prob an extra step or two, was a little more easier for my level of knowledge:

    1. I copied the table structure to a temp table for wp_wpsc_product_list.

    then:
    SELECT ""all fields from wp_wpsc_product_list""", wp_wpsc_productmeta.meta_value

    FROM ((wp_wpsc_product_list LEFT JOIN wp_wpsc_item_category_assoc ON wp_wpsc_product_list.id = wp_wpsc_item_category_assoc.product_id) LEFT JOIN wp_wpsc_productmeta ON wp_wpsc_product_list.id = wp_wpsc_productmeta.product_id)

    WHERE wp_wpsc_productmeta.meta_key = "SKU"

    Exported (using phpmyadmin) that to a file, then imported into my new temp table (which then had the new extra field meta_value)

    Then I did:

    UPDATE temptable
    SET temptable = replace(temp_table, 'HDKLEPTO', meta_value)

    Then renamed my orig wp_wpsc_product_list table to backup, and renamed my temp table to wp_wpsc_products

    Thanks so much


    Originally Posted by Devart Team
    This makes sense

    Code:
    CREATE TABLE wp_wpsc_product_files(
      ID INT PRIMARY KEY,
      Name VARCHAR(255),
      Description VARCHAR(255),
      Additional_Description LONGTEXT
    );
    
    INSERT INTO wp_wpsc_product_files VALUES (1, 'product1', 'description1', 'some text and HDKLEPTO'),
      (2, 'product2', 'description2', 'some text and HDKLEPTO'),
      (3, 'product3', 'description3', 'some text and HDKLEPTO');
    
    CREATE TABLE wp_wpsc_productmeta(
      ID INT PRIMARY KEY,
      Product_ID INT,
      Meta_key VARCHAR(255),
      Meta_Value VARCHAR(255)
    );
    
    INSERT INTO wp_wpsc_productmeta VALUES (1, 1, 'SKU', 'value1'),
      (2, 1, 'SKU', 'value2'),
      (3, 1, 'notSKU', 'value3'),
      (4, 2, 'SKU', 'value4'),
      (5, 3, 'notSKU', 'value5'),
      (6, 3, 'SKU', 'value6'),
      (7, 3, 'SKU', 'value7');
    And our update command is -
    Code:
    UPDATE
      wp_wpsc_product_files f,
      (SELECT
        product_id,
        GROUP_CONCAT(meta_value) meta_value
      FROM
        wp_wpsc_productmeta
      WHERE
        meta_key = 'SKU'
      GROUP BY
        product_id) m
    SET
      f.Additional_Description = REPLACE(f.Additional_Description, 'HDKLEPTO', m.meta_value)
    WHERE
      f.ID = m.product_id;
    Get results -
    Code:
    SELECT ID, Name, Description, Additional_Description FROM wp_wpsc_product_files;
    +----+----------+--------------+-----------------------------+
    | ID | Name     | Description  | Additional_Description      |
    +----+----------+--------------+-----------------------------+
    |  1 | product1 | description1 | some text and value1,value2 |
    |  2 | product2 | description2 | some text and value4        |
    |  3 | product3 | description3 | some text and value6,value7 |
    +----+----------+--------------+-----------------------------+
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    225
    Rep Power
    11
    What was wrong with example?
    Devart Company,
    MySQL management tools
    http://www.devart.com/dbforge/mysql/

IMN logo majestic logo threadwatch logo seochat tools logo