How to replace part of a text field with text stored in another table

[radforms]

Hi All, just wondering someone out there could help me solve a little problem in my mysql database:

Table 1 Name: wp_wpsc_product_files

Fields: ID, Name, Description, Additional_Description



Table 2 Name: wp_wpsc_productmeta

Fields: ID, Product_ID, Meta_key, Meta_Value

The Product_ID in the second table – is the link to 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
with value from wp_wpsc_productmeta meta_value field
join wp_wpsc_product_files and
Wp_wpsc_productmeta using product_id and ID field
Find meta_value where meta_key = “SKU”

note: the additional_description field contains other strings of info that I don't want to loose...only want to replace that one string with the correct value for that particular product by find the value for that product in the productmeta table.

hope this makes sense :)

[improvcornartist]

Can you use the REPLACE() function? Example, REPLACE(additional_description, "HDKLEPTO", wp_wpsc_productmeta.meta_value).

[radforms]

Hi All,

This is what I ended up having to do:

I copied the table structure for wp_wpsc_product_files to a temp table and added an extra field for meta_value as I just could not use the replace command with two tables at same time....dont know if its possible or not but. I then did:

SELECT all fields from wp_wpsc_product_list.name, 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"

Saved the results to a csv file (with phpmyadmin), 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, 'HDKLETO', meta_value)

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