need a sql query to clean up spam bots

[X-or]

Hello,

I need help to clean up spam bots, I have literally thousands of them in my db and I think the easiest way is a sql query

Here is a sample of my db

INSERT INTO `vb_userfield` (`userid`, `temp`, `field1`, `field2`, `field3`, `field4`, `field5`, `field6`, `field7`, `field8`, `field9`, `field10`, `field11`, `field12`) VALUES
(10248, NULL, 'Female', 'Afghanistan', 'RussellVomIL', 'Afghanistan', 'RussellVomIL', '', 'RussellVomIL', '123456', '', 'levitra', '', ''),


We have here a typical spam bot account, they can be easily retrieved because they have `vb_userfield`.`field2`='Afghanistan', as bots often pick the first choice in selector

So I want to update their `vb_user`.`usergroupid` value (to move them from registered to banned group) and I want to update all the `vb_userfield`.`fieldX` to empty them because the content is spam, as well as empty their `vb_user`.`homepage` value (spam too)

Could you help me come up with a sql query? Thank you :)

[Dave]

Fairly easy with an UPDATE query with an INNER JOIN.

UPDATE vb_user AS a
INNER JOIN vb_userfield AS b on a.userid = b.userid
SET a.usergroupid = 8, a.homepage = '', b.field1 = '', b.field2 = '', b.field3 = '', b.field4 = '', b.field5 = '', b.field6 = '', b.field7 = '', b.field8 = '', b.field9 = '', b.field10 = '', b.field11 = '', b.field12 = ''
WHERE b.field2 = 'Afghanistan'

[X-or]

Perfect, these buggers were cleaned up. You have my utmost respect Sir. :)

I didn't know the JOIN command could be used like that, much appreciated, thank you. :)

3240 spam bot accounts cleaned up in one query, got to love sql. :)