Hey there. Glad to see me? I wouldn’t be a true blogger if I weren’t sporadic about making posts. But to make up for my absence I’m going to do something I’ve never done before in this code blog. I’m going to post some code. What I am about to show you is not unknown, it’s not even unintuitive. But if you learned mysql on an older version or your just doing a search for this woefully under published goodie then I will be your best friend.

Here’s the scenario. You have a table with some fields (for simplicity we’ll say a, b, and c) and you have a bunch of data to put into this table. A is your key and may be repeated often in your data. C is the bit piece of information you need to be able to get all values of while maintaining your unique A. Here’s your statement:
INSERT INTO yourTable (a, b, c) VALUES (x, y, z) ON DUPLICATE KEY UPDATE c=CONCAT(c,’,’,VALUES(c));

Let us break it down

• ON DUPLICATE KEY UPDATE

• This tells mysql that if "a" (your key) is duplicated in your data then it should perform an update instead of the insert
• This is preferable to REPLACE INTO in a lot of cases because it doesn't make auto increment fields increment
• c=

• This is the field we're updating. Yeah, ok that was a duh statement, but I'm covering my bases.
• CONCAT(c,',',VALUES(c))

• We're concatenating the values in the parenthesis.
• c = the value already stored in the c field
• ',' = a comma
• VALUES(c) = this is the value we wanted to be in the c field before we discovered that there was a duplicate key.

What we end up is a c field that is a list of comma separated values. Obviously you can your anything you would like as your separator, but for my purposes commas are usually the way to go.