SQL Command Reference

Deactivate all inactive users in the last year:

UPDATE `smf_members` SET `is_activated` = 0, `validation_code` = `dateRegistered`
WHERE (UNIX_TIMESTAMP() - `lastLogin`) / 86400 >= 365
AND (UNIX_TIMESTAMP() - `dateRegistered`) / 86400 >= 365;

Search and replace text in the "body" field (note that it must be updated in 2 places):

UPDATE smf_messages SET body = replace(body,'OLD','NEW');

Append a value to a field, if it's not already present:

UPDATE smf_members SET `additionalGroups` = CONCAT(`additionalGroups` , ",30")
WHERE `ID_GROUP` = 2 AND `additionalGroups` NOT LIKE "%30%";

Or:

UPDATE `xf_user` SET `secondary_group_ids` = CONCAT(`secondary_group_ids` , ",39")
WHERE `user_group_id` = 2 AND `secondary_group_ids` NOT LIKE "%39%";

Figure out who's been logged in the most since registration, and cut out the newest registrations:

SELECT `ID_MEMBER` , `member_name` , `real_name` , FROM_UNIXTIME( `date_registered` ) ,
`total_time_logged_in` , (`total_time_logged_in` / ( UNIX_TIMESTAMP( ) - `date_registered` )) *100
AS time_wasted FROM `smf_members` WHERE total_time_logged_in > 2000 ORDER BY time_wasted DESC;

Find users who linked their accounts more than once:

SELECT `id_member`, COUNT(`id_member`) FROM `smf_milnet_milpoints_log`
WHERE `description` = "Linked to Facebook account." GROUP BY `id_member`
HAVING ( COUNT(`id_member`) > 1 )

Move all records older than 1 day to an archive table:
INSERT IGNORE INTO `smf_ao_messages_archive` SELECT * FROM `smf_ao_messages`
WHERE (UNIX_TIMESTAMP() - `timestamp`) / 86400 >= 1;
DELETE FROM `smf_ao_messages` WHERE (UNIX_TIMESTAMP() - `timestamp`) / 86400 >= 1;

Add an incentive based on Rank (Note to me: Adding incentives with player ID 0 apply to all now!):

INSERT INTO `smf_ao_incentives` (`player_ID`, `value`, `field`, `expiry`) VALUES
(5XXX8, [RANK] / 50 + 1, 'initiative', 1320062400);

Link AO Facebook/Army.ca accounts:

/* Replace all 999999 entries with the user's actual Facebook ID */
/* Replace all 888888 entries with the user's actual Army.ca ID */

/* Remove the old Facebook entry */
DELETE FROM `smf_facebook` WHERE `fb_ID` = 999999 or `army_ID` = 888888;

/* Add a new Facebook Entry */
INSERT INTO `smf_facebook` (`fb_ID`, `army_ID`) VALUES (999999, 888888);

/* Add their Facebook MilPoints total to their Army.ca account */
UPDATE `smf_milnet_milpoints` mp, (SELECT `balance` FROM `smf_milnet_milpoints` WHERE `id_member` = 999999) fbmp SET mp.`balance` = mp.`balance` + fbmp.`balance` WHERE mp.`id_member` = 888888;

/* Delete the old Facebook MilPoints record */
DELETE FROM `smf_milnet_milpoints` WHERE `id_member` = 999999;

/* Copy over their MilPoints Logs */
UPDATE `smf_milnet_milpoints_log` SET `id_member` = 888888 WHERE `id_member` = 999999;