Help with SQL query syntax

[KrisP]

I need the correct syntax for below:

UPDATE user
"Add y to membergroupids"
WHERE NOT FIND_IN_SET(X, membergroupids)
AND posts < 40
AND posts > 15


This is what the query does:

For users not member of x (usergroup id=x) and with postcount between 14 and 40.': Make those users a member of y (usergroup id=y).

Thanks

[Wayne Luke]

The following should work:

UPDATE user SET membergroupids = CONCAT_WS(',',membergroupids,'Y')
WHERE NOT FIND_IN_SET(X,membergroupids)
      AND usergroupid <> Y
      AND posts < 40
      AND posts > 15

[KrisP]

Worked thanks!

[KrisP]

I get below (in different situations) for all users where I inserted the Y usergroup (77). All those users have:

primary id = 2 (registered users)
membergroupids = 0,77

Could the error be the ',' in "CONCAT_WS(',',membergroupids,'Y')" - eg. when membergroupids are empty the comma shouldnt be there.

How do I correct this ?

Database error in vBulletin 3.6.4:
Invalid SQL:
SELECT title, usergroupid FROM TGO_usergroup WHERE usergroupid IN (2,,77) ORDER BY title;
MySQL 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 '77) ORDER BY title' at line 1
Error Number : 1064
Date : Wednesday, November 5th 2008 @ 03:15:55 PM

[Wayne Luke]

Error is that the 0 is being removed by your system. You can try CONCAT(membergroupids,Y) but you would have to handle the commas to delimit values manually.

[Wayne Luke]

What version of MySQL are you using?

[KrisP]

MySQL 4.1.12-Debian_0.dotdeb.0-log

[KrisP]

Couldnt I just break it up in two querys:

UPDATE user SET membergroupids = CONCAT_WS(membergroupids,'Y')
WHERE NOT FIND_IN_SET(X,membergroupids)
AND usergroupid <> Y
AND posts < 40
AND posts > 15
AND membergroupids = 0

afterwards

UPDATE user SET membergroupids = CONCAT_WS(',',membergroupids,'Y')
WHERE NOT FIND_IN_SET(X,membergroupids)
AND usergroupid <> Y
AND posts < 40
AND posts > 15
AND membergroupids <> 0

Is the bold the correct syntax?

With regards to the other problem. I have 309 rows - I guess I can edit them manually but its gonna take a while.

[Wayne Luke]

You could use two queries like that. If you were using MySQL 5 you could do it in one batch with subqueries which is why I asked the version.

membergroupids should be a string though so you would have to do string comparison, not number comparison.

[KrisP]

For reference:

Follwoing mysql query solved the problem:

UPDATE user SET membergroupids = "77"
WHERE membergroupids = ",77"