0

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

2
Contributors
12
Replies
27
Views
4 Years
Discussion Span
Last Post by mpc123
0

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.

Edited by pritaeas

0

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

0
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.

0

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

0

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

0

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

0

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

Edited by mpc123: wrong info

0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.