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.

PHP Code:

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.