mysql select update

Question

Hi

I have read quite a few selcet+update questions in here but cannot understand how to do it. So will have to ask from the beginning.

I would like to update a table based on data in another table. Setup is like this:

- TABLE a ( int ; string )
ID     WORD
1      banana
2      orange
3      apple

- TABLE b ( "comma separated" string ; string )
WORDS  TEXTAREA
0      banana                  -> 0,1
0      orange apple apple      -> BEST:0,2,3 ELSE 0,2,3,3
0      banana orange apple     -> 0,1,2,3

Now I would like to for each word in TABLE a append ",a.ID" to b.WORDS like:

SELECT id, word FROM a
(for each) -> UPDATE b SET words = CONCAT(words, ',', a.id) WHERE b.textarea like %a.word%

Or even better: replace the word found in b.textarea with ",a.id" so it is the b.textarea that ends up beeing a comma separeted string of id's... But I do not know if that is possible.

Tried this but not working. But I think I am getting closer:

UPDATE a, b
SET b.textarea =
replace(b.textarea,a.word,CONCAT(',',a.id))
WHERE a.word IN (b.textarea)
ORDER BY length(a.word) DESC

Answer 1

I ended up doing a work-a-round. I exported all a.words to excel and created an update for each row like this:

UPDATE `tx_ogarktiskdocarchive_loebe` SET `temp_dictionay` = replace(lower(temp_dictionay) , lower('Drygalski’s Grønlandsekspedition'), CONCAT(',',191));

Then I pasted the aprox 1000 rows into ans sql file and executed it. Done.

Answer 2

I had to do "a cleaner double post" of this one to get the answer.

A solution can be put together based on this manual: http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat

GROUP_CONCAT will make a comma separated string based on the fileds it shall CONCAT. Perfect. And regarding the preferred solution with no dublicates in the result there is this example in the manual that will filter out dublicates using DISTINCT inside the GROUP_CONCAT:

mysql> SELECT student_name,
    ->     GROUP_CONCAT(DISTINCT test_score
    ->               ORDER BY test_score DESC SEPARATOR ' ')
    ->     FROM student
    ->     GROUP BY student_name;