XF 2.1 SQL help to delete warning points prior to a specified date

[djbaxter]

On a vBulletin to XF conversion (where some of the vB tables may have been corrupted), a residual issue is that there are old unexpired vBulletin warning points left over. As a result, if some members get a 1-point warning via XF, it puts them over the threshold and temporarily bans them.

I need an SQL query to remove warning points prior to December 31, 2018.

Can anyone help with the query?

I have been doing this manually, for example:

SELECT * FROM `xf_user` WHERE `username` = "{membername}";
UPDATE `xf_user` SET `warning_points` = '3' WHERE `xf_user`.`user_id` = {12345};

but obviously this is time-consuming and cumbersome. I'd like a query to find ALL warning points prior to December 31, 2018 and delete those points.

 

[djbaxter]

Bump.

 

[Xon]

This will reset all user's warning points to be derived from the XF warning system, counting non-expired warnings. Note; if there are warnings past their expiry time but not explicitly expired they will be included so XF's warning expiry system works properly.

update xf_user
set warning_points = COALESCE(
   (select sum(xf_warning.points) from xf_warning where xf_warning.user_id = xf_user.user_id and is_expired = 0)
, 0);

 

[djbaxter]

Thank you. That may turn out to be the best solution. Let everyone start fresh.