How to insert a value after values already in the column mysql

Question

In my user table, I have a userid column, and membergroupids column. Here is the query I have so far.

UPDATE `user`
SET `membergroupids`=`,100`
WHERE `userid` IN
(
  312 , 331 , 538 , 528 , 77 , 80 , 293 , 343 , 609 , 607 , 547 , 596 , 216 ,
  780 , 1 , 884 , 430 , 500 , 881 , 952 , 957 , 979 , 352 , 994 , 955 , 776 ,
  744 , 904 , 1038 , 76 , 1083 , 61 , 202 , 1001 , 1173 , 1176 , 418 , 770 ,
  1192 , 191 , 1211 , 1206 , 1247 , 402 , 423 , 584 , 1233.1282 , 167 , 1329,
  1330 , 376 , 1227 , 146 , 1113 , 1327 , 1352 , 254 , 1065 , 1406 , 1225 ,
  1230 , 1405 , 1443 , 812 , 1295 , 1510 , 1571 , 1565 , 1468 , 1349 , 1645 ,
  501 , 1312 , 1718 , 13 , 200 , 1177 , 1785 , 107 , 540 , 1591 , 1508 , 59 ,
  1867 , 222 , 1520 , 782 , 26 , 332 , 474 , 1837 , 1828 , 1431 , 1951 , 1953 ,
  1950 , 847 , 1477 , 1726 , 1669 , 1185 , 1955 , 1872 , 1926 , 1392 , 853 ,
  1892 , 2020 , 1511 , 2027 , 2197 , 2184 , 2134 , 2239 , 119 , 122
)

So basically, what I am trying to do is.. I am trying to make it so the query adds ",100" to the column membergroupids if their userid is one of the ones listed in ().
However, here is the thing. All of these users already have values listed in membergroupids.
For example, the user 312 has these values already in the column membergroupids:
35,49,58,61,71,38,59
So, this query needs to do this to his field:
35,49,58,61,71,38,59,100

..and for all of the other userids listed.

Thanks if anyone can help me. I'm sure this is on google but I didn't know how to word it.

Those should be straight single quotes `',100'` rather than backticks. but this is really the wrong approach which will eventually come back to cause you headaches. You should be using a separate table which joins member ids to group ids indicating group membership, rather than storing these as a string of values. — Michael Berkowski, Mar 30 '13 at 14:24
But if you _must_ do it this way, you need to concatenate the new value onto the old: `SET membergroupids = CONCAT(membergroupids, ',100')` — Michael Berkowski, Mar 30 '13 at 14:25
Hi Michael. I am using vBulletin, and this is how they have it set up. As you can see, theres over 100? userids there. I could just select from the user table where the userid equals all of those numbers, and then it shows me their usernames and I can manually add them to the usergroup with id '100', however, doing over 100 would take a very long time.. that is why I am trying the SQL approach. I just need to know how to add 100 to their membergroupids without erasing all of their current membergroupids. Thanks — llw, Mar 30 '13 at 14:26

Michael Berkowski · Accepted Answer · 2013-03-30T14:33:29.360

Since you're in the unfortunate position of not being able to change the schema, you can add the value by concatenating it onto the existing values via CONCAT()

UPDATE `user` SET `membergroupids` = CONCAT(`membergroupids`, ',100') WHERE...

That will force ,100 onto every user, even if they currently have an empty membergroupids. To account for the empty ones, you can use a CASE condition:

UPDATE `users`
SET `membergroupids` =  
  /* Concatenate ,100 onto the non-empty ones */
  CASE WHEN `membergroupids` IS NOT NULL AND `membergoupids` <> '' 
    THEN CONCAT(`membergroupds`, ',100')
  /* And set the empty ones to just 100 (no comma) */
  ELSE '100'
  END
WHERE ...

In an ideal world, the user <--> group memberships would be handled with a proper many-to-many relationship, whereby a third table is used to assign users to groups, having two columns: userid, groupid.

Thank you so much. Worked like a charm. Saved me 115 manual entries. Gotta love queries :D — llw, Mar 30 '13 at 14:31

score 1 · Answer 2 · edited May 23 '17 at 12:13

UPDATE `user` SET `membergroupids`=CONCAT(`membergroupids`, ',100') 
WHERE `userid` IN ( ... )

Of course this assumes you have at least one membergroupid in the list already. Appending ',100' if the list is empty results in a list with a leading comma, which is not usually desirable. Here's a quick solution:

UPDATE `user` SET `membergroupids`=CONCAT_WS(COALESCE(`membergroupids`, ''), '100') 
WHERE `userid` IN ( ... )

Also what happens if the list of membergroupids grows too long, and can't fit in the length limit for the varchar column? "I'm sorry, you can't join that group because we can't store your list of group memberships. Perhaps you'd like to join some groups with shorter values for their group ids, then you could join more groups."

Storing lists of values in a comma-separated string is a bad design for a relational database. See a description of pitfalls in my answer to Is storing a delimited list in a database column really that bad?

It would be easier to add a membergroup for a user if you stored the memberships one per row in an intersection table. Then you don't need to know the current list of values.

INSERT INTO user_membergroups (userid, groupid) 
SELECT `userid`, 100 FROM `user`
WHERE `userid` IN ( ... )

I agree Bill, however, the way vBulletin has their database set up.. sadly I can't do much to change it as it is hard coded. But I will definitely ask what happens if there are too many values in the membergroupids field as I can see that being an issue in the future.. oi — llw, Mar 30 '13 at 14:32
Okay, I'll cross vBulletin off my list of apps I would recommend. :-( — Bill Karwin, Mar 30 '13 at 14:34

How to insert a value after values already in the column mysql

2 Answers2