Query to remove signatures that have links in them for vb4

[tpearl5]

I just used this to batch remove signatures that have links in them. 95% of them were spam accounts anyway:

UPDATE usertextfield SET signature=NULL WHERE signature LIKE '%url%';

The entire signature will be removed, not just the link

This will probably work for vb3 as well, but I haven't tested it.

[tpearl5]

And a slightly more complex query if you want to remove signatures from a specific user group:

UPDATE usertextfield AS ut
INNER JOIN user AS u ON ut.userid = u.userid SET signature=NULL
WHERE ut.signature LIKE '%url%' AND u.usergroupid = 2;


Replace 2 with the user group you want to remove signatures for.

[scottct1]

How do I just remove ALL signatures from the Registered usergroup?

[tpearl5]

How do I just remove ALL signatures from the Registered usergroup?


UPDATE usertextfield AS ut
INNER JOIN user AS u ON ut.userid = u.userid SET signature=NULL
WHERE u.usergroupid = 2;



I haven't tested this, I would run it as a SELECT statement first to make sure that you are getting the right data, like this:


SELECT * FROM usertextfield AS ut
INNER JOIN user AS u ON ut.userid = u.userid
WHERE u.usergroupid = 2;


Do a backup! Once it's gone it's gone!

[vBNinja]

How do I just remove ALL signatures from the Registered usergroup?

assuming you don't have a table prefix:


UPDATE usertextfield
JOIN user USING (userid)
SET signature = ''
WHERE usergroupid = 2;


otherwise you'll need to add the prefix before "usertextfield" and "user"

But that should work fine if you're using phpmyadmin

[ForceHSS]

Confirm vBNinja (https://vborg.vbsupport.ru/member.php?u=434604)query works tested on navicat

[tpearl5]

assuming you don't have a table prefix:


Ah, much cleaner. Is signature supposed to be an empty string vs null?

[vBNinja]

Ah, much cleaner. Is signature supposed to be an empty string vs null?

Doesn't matter