Soft delete all moderated posts (queries)

[Bi0S Emerald Member Posts:12,183 Threads:12,128 Reputation 31]

You can run these queries to soft delete all moderated posts. You need to change the red pieces to attribute these deletions to a user_id and username, as well as give the reason. These things are recorded in the deletion log:

Code:

INSERT INTO xf_deletion_log (content_type, content_id, delete_date, delete_user_id, delete_username, delete_reason)
    SELECT content_type, content_id, UNIX_TIMESTAMP(), 1, 'admin', 'manually moved from moderation to soft delete'
    FROM xf_moderation_queue AS mq
    WHERE mq.content_type = 'post';

DELETE
FROM xf_moderation_queue
WHERE content_type = 'post';

UPDATE xf_post
SET message_state = 'deleted'
WHERE message_state = 'moderated';

And here are the queries to soft delete all moderated threads:

Code:

INSERT INTO xf_deletion_log (content_type, content_id, delete_date, delete_user_id, delete_username, delete_reason)
    SELECT content_type, content_id, UNIX_TIMESTAMP(), 1, 'admin', 'manually moved from moderation to soft delete'
    FROM xf_moderation_queue AS mq
    WHERE mq.content_type = 'thread';

DELETE
FROM xf_moderation_queue
WHERE content_type = 'thread';

UPDATE xf_thread
SET discussion_state = 'deleted'
WHERE discussion_state = 'moderated';

You also need to run this query to reset the count in the moderator bar:

Code:

DELETE
FROM xf_data_registry
WHERE data_key = 'moderationCounts';

Backup first.