replace part of field with FIND IN SET

[mpc123]

Hi I have an issue where by I have lots of products in a field which are comma delimited I want to replace those product words with a id number that I have in a different table.
I currently use FIND INSET to replace a tab to the comma but currently cant see how I would you that type to change what I need to.

Here is my data

EG
Table 1
product_field
car,boat,shirt,tie

Table 2

id product_name
1 car
2 boat
3 shirt
4 tie

So I need to replace Table 1 product_field with Table 2.id so result would be

Table 1

product_field
1,2,3,4

Can anyone help please

[pritaeas]

Perhaps some JOIN can do that. If you use GROUP_CONCAT on both fields in table 2 it may be possible. I'll need to know more about the table structures. How are both tables linked? Only by the comma separated column?

Another option is to build a specific REPLACE.

[mpc123]

they are not joined
the TABLE 1 fields to use would be ID and product_name

[mpc123]

SORRY

the TABLE 2 fields to use would be ID and product_name

[pritaeas]

SELECT *, GROUP_CONCAT(id) 
FROM replace2 t2
LEFT JOIN replace1 t1 ON FIND_IN_SET(t2.product_name, t1.product_field)
GROUP BY t1.product_field

It'll get you started, just figure out how to do the update.

[mpc123]

firstly thankls for the reply but sorry im not that advanced, so i dont know how I would update from that

[pritaeas]

Use that query to create a temporary table. Then use the temporary table to help do the UPDATE in the original table.

[mpc123]

OK thanks i will try can you tell me what the replace2 represents please, sorry for being thick

[pritaeas]

Your tables 1 and 2.

[mpc123]

so line 2 would be

FROM TABLE 2 NAME TABLE 2 NAME ?

[pritaeas]

Replace replace1 with table1, same for 2.

[mpc123]

i have got a result to come out after running that query but jusy with one row for now out of the thousands of rows

EG it groups the first one in the field and not the rest seperated by , . So where by it should select multiple id's it only selects the first one it finds in the set. How will i make it select all ids for all of the sets

[mpc123]

its ok i replaced the GROUP_CONCAT(id) with GROUP_CONCAT(Table.id) and this now works, now to try and do the update?