• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1110
  • Last Modified:

MySql update with concat and find_in_set

Anyone know why the sql below should give an error please?
update table admin_files set admin_groups_id = concat(admin_groups_id,',','7') where find_in_set('2', admin_groups_id)

Open in new window

0
ncw
Asked:
ncw
  • 5
  • 5
  • 2
1 Solution
 
ajcheung78Commented:
What error are you getting and what is does the function find_in_set do?
0
 
ajcheung78Commented:
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.
0
 
ncwAuthor Commented:
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.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
ncwAuthor Commented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
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;

Open in new window

0
 
ncwAuthor Commented:
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;

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
"does MySql not assume a value greater than zero is a true?" < I believe it does. I bounce between SQL platforms and always air on the side of portability, but YES MySQL will treat that as true.

I am just BLIND and did not see any significant errors, so I suggested the > 0. Now that I put my computer glasses on, I see you have UPDATE TABLE <tablename>. That is incorrect. It should be UPDATE <tablename>.

update admin_files set admin_groups_id = concat(admin_groups_id,',',4) where find_in_set(2, admin_groups_id) > 0;
0
 
ncwAuthor Commented:
I've solved it, it works when I remove the reference to 'table'.
0
 
ncwAuthor Commented:
We found the 'table' reference at the same time. Thanks.
0
 
Kevin CrossChief Technology OfficerCommented:
You mean like I said above? Or some other way.
0
 
Kevin CrossChief Technology OfficerCommented:
Ah, never mind, I see you were talking about the same thing. Glad you found it.
Best regards and happy coding,

Kevin
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now