SQL Command Reference

Oct

26

2016

0

Time to read

1 minute

Read so far

By crypto

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%";
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;

Count the number of OTRS tickets for a given customer within the last 12 months:

SELECT * FROM `ticket` WHERE (`ticket`.`customer_id` = "FMR" OR `ticket`.`customer_user_id` LIKE "%fmr%" OR `ticket`.`customer_user_id` LIKE "%fidelity%") and `ticket`.`create_time_unix` > UNIX_TIMESTAMP(NOW() - INTERVAL 1 YEAR);

Count the total number of OTRS articles for a given customer within the last 12 months:

SELECT * FROM `ticket`,`article` WHERE (`ticket`.`customer_id` = "FMR" OR `ticket`.`customer_user_id` LIKE "%fmr%" OR `ticket`.`customer_user_id` LIKE "%fidelity%") and `ticket`.`create_time_unix` > UNIX_TIMESTAMP(NOW() - INTERVAL 1 YEAR) and `article`.`ticket_id` = `ticket`.`id`;