MySql update with concat and find_in_set

What error are you getting and what is does the function find_in_set do?

Seems like you should be able to edit your own comments - but in any event - what are you attempting to do exactly as your where clause does not make sense with the function that you are using.

The admin_groups_id field values are lists eg 1,2 or 1,2,3 (yes I know this goes against good design), and I want to append the new value of 7 to each value list where the value list contains the value of 2.

The data is defining access levels for files so there is one record/row per file.

I've found that the field I'm trying to update is of type 'set'. I've got code that will add a value to the set value list, but how would I then change which values are selected, or add the value of 7 as selected.

find_in_set() is not a boolean, it returns the position in the set the value you requested is. Therefore, you need to make the WHERE clause condition something like:

find_in_set('2', admin_groups_id) > 0

Also, what happens if the SET already contains 7. Therefore, the complete code may look similar to this:

SET @set = '1,2,3';
SELECT CONCAT(@set, ',', 7)
FROM DUAL
WHERE FIND_IN_SET(2, @set) > 0
AND FIND_IN_SET(7, @set) = 0;

Select allOpen in new window

mwvisa1:
Some other existing code with find_in_set doesn't cause an error even though there is no > 0, does MySql not assume a value greater than zero is a true?

I take your point about the need to avoid adding a value that already exists, however that can not occur because of how the system works, but it would be good practice to include the check.  

Your example code is a 'select' but I'm doing an 'update', maybe that is not significant to the issue, maybe the only significant difference is the > 0. I tried the code below but it stills gives the following error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table admin_files set admin_groups_id = concat(admin_groups_id,',',4) where find'

update table admin_files set admin_groups_id = concat(admin_groups_id,',',4) where find_in_set(2, admin_groups_id) > 0;

Select allOpen in new window

I've solved it, it works when I remove the reference to 'table'.

We found the 'table' reference at the same time. Thanks.

You mean like I said above? Or some other way.

Ah, never mind, I see you were talking about the same thing. Glad you found it.
Best regards and happy coding,

Kevin