sql - MySQL Column and Row entry removal -

partial view of database screen shot

ok before start let me state self taught , make alot of mistakes. not strong in sql query writing. ok want update table removing multiple membergroupids. dont want in membergroupids 2, 192, 10, 20. ok can tell still visible commas have worked basic code 1 group @ time , leaves commas. have on 120 membergroupids need clear on 1800 rows. need able put in piece worry once can done first.

ok review. need able remove above mentioned membergroupids multiple rows in table. if need explain clearer please let me know still trying figure out sql stuff.

update user inner join userfield on user.userid = userfield.userid set `membergroupids` = replace(ltrim(rtrim(replace(concat(' ', replace(`membergroupids`, ',', ' '), ' '), ' 2 ', ' '))), ' ', ',') find_in_set('2', `membergroupids`) > 0 , `field23` <= 0 , (`membergroupids` '%' ',2,' '%' or `membergroupids` '2,' '%' or `membergroupids` '%' ',2' or `membergroupids` '2');

that code myself , friend came 1 membergroupids @ time.

is membergroups single field? and, in table have number of records may have same membergroup , you're looking way remove them all? that's how understand correct me if i'm wrong.

i use like:

delete table membergroups in (2, 192, 10, 20) 

it might not possible, better have membergroupids in separate table 1 row represents each membergroupid each user belongs to. remove them simple delete rather complicated set update.

the query example guys came similar i'd particular setup, can see can messy after awhile.

on other hand, if can add membergroupids separate table setup like:

create table user_groups( userid  int, membergroupid int); 

then can remove membergroups quite simply.

example 1: remove member groups 2, 192, 10 , 20 users

delete user_groups membergroupid in (2, 192, 10, 20) 

example 2: remove member groups 2, 192, 10 , 20 users not members of groups 2 , 22.

delete user_groups membergroupid in (2, 192, 10, 20) , userid not in (select userid user_groups membergroupid in (2,22)); 

this setup, use separate table instead of field containing set of values may seem less efficient, it's not. plus, have more flexibility when comes adding/removing membergroups , don't have deal commas, concats, etc.