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_Desc ription



Table 2 Name: wp_wpsc_product meta

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_product meta 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_desc ription 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_desc ription 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_desc ription
Replace within additional_desc ription HDKLEPTO
with value from wp_wpsc_product meta meta_value field
join wp_wpsc_product _files and
Wp_wpsc_product meta using product_id and ID field
Find meta_value where meta_key = “SKU”

note: the additional_desc ription 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(additio nal_description , "HDKLEPTO", wp_wpsc_product meta.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_product meta.meta_value

FROM ((wp_wpsc_produ ct_list LEFT JOIN wp_wpsc_item_ca tegory_assoc ON wp_wpsc_product _list.id = wp_wpsc_item_ca tegory_assoc.pr oduct_id) LEFT JOIN wp_wpsc_product meta ON wp_wpsc_product _list.id = wp_wpsc_product meta.product_id )

WHERE wp_wpsc_product meta.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_ta ble, 'HDKLETO', meta_value)

Then renamed my orig table to backup, and renamed my temp table to wp_wpsc_product s