MYSQL: Update field with concat of multiple fields

Question

I'm trying to update a field of my table with the CONCAT of the some fields of the same table.

Whith this

UPDATE tabex SET field1=CONCAT(tabex.a1,', ',tabex.a2,', ',tabex.a3,', ',tabex.a4,', ',tabex.a5,', ',tabex.a6,', 'tabex.a7,', ',tabex.a8,', ',tabex.a9 );

This query has 0 rows affected and no errors.

With this other query

UPDATE tabex SET field1=CONCAT_WS(tabex.a1,', ',tabex.a2,', ',tabex.a3,', ',tabex.a4,', ',tabex.a5,', ',tabex.a6,', 'tabex.a7,', ',tabex.a8,', ',tabex.a9 );

If the content of some of a(n) fields is NULL mysql puts a copy of the previous result

Someone can help me?

since ur update has no where clause i have to ask : is the table empty? — Stephan
– Stephan, Commented Nov 7, 2013 at 12:48
How big is data in your fields? (field that you're trying to update & fields from which you're gathering your CONCAT) — Alma Do
– Alma Do, Commented Nov 7, 2013 at 12:49
I try to update field1 with the concat content of the a(n) fields of the same row. I need to add a where clause? — Hanzo
– Hanzo, Commented Nov 7, 2013 at 12:52

fancyPants · Accepted Answer · 2013-11-07 13:18:42Z

5

When this query

UPDATE tabex SET field1=CONCAT(tabex.a1,', ',tabex.a2,', ',tabex.a3,', ',tabex.a4,', ',tabex.a5,', ',tabex.a6,', 'tabex.a7,', ',tabex.a8,', ',tabex.a9 );

doesn't affect a row, the only explanation would be, that the table is empty. It would update every row in the table. But if one of the columns is NULL, your field1 column will also be NULL.
To avoid that, you have to use the COALESCE() function. This function returns the first of its parameters which is not NULL.

UPDATE tabex SET field1=CONCAT(COALESCE(tabex.a1, ''),', ',...);

On a sidenote I have to ask, why you want to do this. Comma separated values in columns are a bad idea most of the times.

And finally, your query using CONCAT_WS() is wrong. The _WS in the function name is short for "with separator", so the first parameter is the separator which then is placed between the other parameters of the function. So you should write it like this:

UPDATE tabex SET field1=CONCAT_WS(',', tabex.a1, tabex.a2, tabex.a3,...);

Another advantage of the CONCAT_WS() function is, that it ignores NULL values. Read more about the two functions in the manual.

answered Nov 7, 2013 at 13:18

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hanzo Over a year ago

Thanks @fancyPants, the right sintax of CONCAT_WS works for me. I know that comma separated values is a bad idea but I cant modify the DB structure.

Collectives™ on Stack Overflow

MYSQL: Update field with concat of multiple fields

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related