MYSQL query to update a field using CONTAINS

Question

I want to make a query in mySQL where I need to update "processes_handled" of department_table with the concatenated contents from "process_name" of process_table, if the department_name is included in the "departments_handling_the_process" field.

This is what my problem looks like:

department_table    

department_name | processes_handled  
Department 1    | Process A; Process B; Process D;  
Department 2    | Process A; Process C: Process E; Process G;  
Department 3    | Process C; Process D; Process F 




process_table   

process_name    | departments_handling_the_process  
Process A   | Department 1; Department 2  
Process B   | Department 1  
Process C   | Department 2; Department 3  
Process D   | Department 1; Department 3  
Process E   | Department 2  
Process F   | Department 3  
Process G   | Department 2

my current update statement looks like:

UPDATE department_table,process_table
SET processes_handled= (SELECT GROUP_CONCAT(`process_name SEPARATOR '; ')
                        FROM process_table
                        WHERE CONTAINS(process_table.departments_handling_the_process, department_table.department_name))

WHERE CONTAINS(process_table.departments_handling_the_process, department_table.department_name)
;

I hope you can help me fix my statement. Thanks and more power!

thanks for your response! however I don't have a problem with delimited lists in database columns. — Rezard, May 22 '14 at 10:40
I appreciate your concern sir, but I still need help on the current situation and not yet on the delimited lists. Thanks! — Rezard, May 22 '14 at 10:45
I didn't want to turn this into a discussion, but since you seem to have missed my point: Working with delimited lists is one ugly mess and hard to handle. There are only two reasons one would use them. 1. One doesn't know better. 2. In high-performance data warehouses in denormalized dimensions. And even this is rather rare. The fact, that you didn't get an answer yet might as well be a result of it being such a nightmare. Rework your database design and you'll be surprised how incredibly easy your task is usually solved. — fancyPants, May 22 '14 at 11:35
With all due respect I beleive that all that you have stated is true (and I will keep it in mind). However, I still want to get an answer to the problem at hand, regardless of the current setup being a nightmare or not. Many thanks! — Rezard, May 23 '14 at 03:52
Okay, but your question is not yet clear to me. What's the desired result? It seems to me, that the process_handled column in department_table is already the desired result. Or is it empty right now? — fancyPants, May 23 '14 at 09:26
Hi, yes it is the desired result. My problem is when one of the tables are updated, the other one also needs to reflect the change.Thank you so much! — Rezard, May 23 '14 at 10:02
And here we are with another downside. Usually this is solved with a simple trigger. Now you have to read the whole table again and again for each update. Actually you have to update the whole department table and for each row you have to read the process table. This is not only unnecessary work, it's also a performance killer. See where this is leading? — fancyPants, May 23 '14 at 10:04

score 0 · Accepted Answer · answered May 23 '14 at 09:56

Assumption based on this comment:

Okay, but your question is not yet clear to me. What's the desired result? It seems to me, that the process_handled column in department_table is already the desired result. Or is it empty right now?

Sample data:

CREATE TABLE department
    (`department_name` varchar(12), `processes_handled` varchar(43))
;


INSERT INTO department
    (`department_name`, `processes_handled`)
VALUES
    ('Department 1', ''),
    ('Department 2', ''),
    ('Department 3', '')
;


CREATE TABLE process
    (`process_name` varchar(9), `departments_handling_the_process` varchar(26))
;

INSERT INTO process
    (`process_name`, `departments_handling_the_process`)
VALUES
    ('Process A', 'Department 1; Department 2'),
    ('Process B', 'Department 1'),
    ('Process C', 'Department 2; Department 3'),
    ('Process D', 'Department 1; Department 3'),
    ('Process E', 'Department 2'),
    ('Process F', 'Department 3'),
    ('Process G', 'Department 2')
;

Solution / update statement:

update
department
set processes_handled = (select group_concat(process_name separator '; ')
                       from process where find_in_set(department_name, replace(departments_handling_the_process, '; ', ',')) != 0);

Result:

select * from department;

| DEPARTMENT_NAME |                          PROCESSES_HANDLED |
|-----------------|--------------------------------------------|
|    Department 1 |            Process A; Process B; Process D |
|    Department 2 | Process A; Process C; Process E; Process G |
|    Department 3 |            Process C; Process D; Process F |

Explanation:

There is no contains function in MySQL. Use find_in_set() instead. Also note, that you have to replace the ; with , so that find_in_set() works correctly. You might stumble over some more of such "traps". Some you'll notice, some you might not (at once). Here's the output when leaving out the replace() function:

| DEPARTMENT_NAME |    PROCESSES_HANDLED |
|-----------------|----------------------|
|    Department 1 |            Process B |
|    Department 2 | Process E; Process G |
|    Department 3 |            Process F |

So be prepared for heavy testing and debugging. Also, in other situations you might be forced to write a function to split the comma separated column and spread it over multiple rows. There are enough examples here on stackoverflow where this is the only option. So again (but this time the last time), you will have much trouble with this database design.

read more about the functions used here
see it working live in an sqlfiddle

Thank you so much. I used the following user-defined function to segregate the delimited strings: CREATE FUNCTION SPLIT_STR( x VARCHAR(255), delim VARCHAR(12), pos INT ) RETURNS VARCHAR(255) RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, ''); — Rezard, May 23 '14 at 10:10

MYSQL query to update a field using CONTAINS

1 Answers1