From a06a38163e75895eacb209196bd35b1fcab54f08 Mon Sep 17 00:00:00 2001 From: megamage Date: Fri, 19 Dec 2008 16:58:38 -0600 Subject: *Sql queries to retain you arena team ratings. By Alex. 1. Flush arena points on the server (revs before 539) 2. Stop server 3. Apply new arena patch and sql queries in rev 541. 4. Apply SQL queries in 542. 5. Start server Backup your characters data and then do exactly the five steps, otherwise you may lose your characters' arena ratings. --HG-- branch : trunk --- sql/updates/542_characters.sql | 74 ++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 74 insertions(+) create mode 100644 sql/updates/542_characters.sql (limited to 'sql') diff --git a/sql/updates/542_characters.sql b/sql/updates/542_characters.sql new file mode 100644 index 00000000000..cf5e85dbd31 --- /dev/null +++ b/sql/updates/542_characters.sql @@ -0,0 +1,74 @@ +UPDATE + `arena_team_member` AS `a`, + (SELECT + `attq1`.`guid` AS `b_guid`, + `atid`, `atpr` + FROM + (SELECT + `guid`, + `name`, + SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',1545),' ',-1) AS `atid`, + SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',1550),' ',-1) AS `atpr` + FROM + `characters`) AS `attq1` + INNER JOIN + `arena_team_member` ON `arenateamid` = `atid` AND + `arena_team_member`.`guid` = `attq1`.`guid` + WHERE + `atid` <> 0) + AS `b` +SET + `a`.`personal_rating` = `b`.`atpr` +WHERE + `a`.`arenateamid` = `b`.`atid` AND + `a`.`guid` = `b`.`b_guid`; + +UPDATE + `arena_team_member` AS `a`, + (SELECT + `attq1`.`guid` AS `b_guid`, + `atid`, `atpr` + FROM + (SELECT + `guid`, + `name`, + SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',1551),' ',-1) AS `atid`, + SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',1556),' ',-1) AS `atpr` + FROM + `characters`) AS `attq1` + INNER JOIN + `arena_team_member` ON `arenateamid` = `atid` AND + `arena_team_member`.`guid` = `attq1`.`guid` + WHERE + `atid` <> 0) + AS `b` +SET + `a`.`personal_rating` = `b`.`atpr` +WHERE + `a`.`arenateamid` = `b`.`atid` AND + `a`.`guid` = `b`.`b_guid`; + +UPDATE + `arena_team_member` AS `a`, + (SELECT + `attq1`.`guid` AS `b_guid`, + `atid`, `atpr` + FROM + (SELECT + `guid`, + `name`, + SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',1557),' ',-1) AS `atid`, + SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',1562),' ',-1) AS `atpr` + FROM + `characters`) AS `attq1` + INNER JOIN + `arena_team_member` ON `arenateamid` = `atid` AND + `arena_team_member`.`guid` = `attq1`.`guid` + WHERE + `atid` <> 0) + AS `b` +SET + `a`.`personal_rating` = `b`.`atpr` +WHERE + `a`.`arenateamid` = `b`.`atid` AND + `a`.`guid` = `b`.`b_guid`; \ No newline at end of file -- cgit v1.2.3