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]

Originally Posted by scottct1

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]

Originally Posted by scottct1

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

assuming you don't have a table prefix:

PHP Code:

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 query works tested on navicat

[tpearl5]

Originally Posted by vBNinja

assuming you don't have a table prefix:

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

[vBNinja]

Originally Posted by tpearl5

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

Doesn't matter