SQL help needed

[Mortykins]

Well, took note that all of my Special Attacks, are now Special_Abilities, and they are all NULL... I saw the script for converting the npcspecialattks to Special_Abilities however it was too late the auto update script myst have gone in and simply DELETED all of them.. I of course have backups of the old database before that happened but I am not the greatest with SQL.

This is what I want to do but not sure how:

Export the Column 'npcspecialattks' from the backup
Import the Column into a Test Copy of the live table.
Run the script to convert the 'npcspecialattks' to 'Special_Abilities" found in the git SQL script "2013_07_11_NPC_Special_abilities.sql"

Can someone please help me with this ?

Thank you in advance,

Morty

[Akkadius]

Well, took note that all of my Special Attacks, are now Special_Abilities, and they are all NULL... I saw the script for converting the npcspecialattks to Special_Abilities however it was too late the auto update script myst have gone in and simply DELETED all of them.. I of course have backups of the old database before that happened but I am not the greatest with SQL.

This is what I want to do but not sure how:

Export the Column 'npcspecialattks' from the backup
Import the Column into a Test Copy of the live table.
Run the script to convert the 'npcspecialattks' to 'Special_Abilities" found in the git SQL script "2013_07_11_NPC_Special_abilities.sql"

Can someone please help me with this ?

Thank you in advance,

Morty

It's probably not so much the fault of the auto updater rather than it is your database had something that bombed on one of the following statements before actually performing the concatenation necessary in the new field before it dropped the old field.

Manually restore the old field for all of your NPC's and then run the concatenation statements and you should be fine, not too terrible to fix.

If you have a copy of the old table, it should be fairly simple to do this, if you can't do it shoot me the tables and I can do it for you and generate a SQL that you can run against your database now without needing to do anything else.

ALTER TABLE `npc_types` ADD COLUMN `special_abilities` TEXT NULL AFTER `npcspecialattks`;
ALTER TABLE `npc_types` MODIFY COLUMN `special_abilities` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;

UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "1,1^") WHERE npcspecialattks LIKE BINARY '%S%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "2,1^") WHERE npcspecialattks LIKE BINARY '%E%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "3,1^") WHERE npcspecialattks LIKE BINARY '%R%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "4,1^") WHERE npcspecialattks LIKE BINARY '%r%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "5,1^") WHERE npcspecialattks LIKE BINARY '%F%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "6,1^") WHERE npcspecialattks LIKE BINARY '%T%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "7,1^") WHERE npcspecialattks LIKE BINARY '%Q%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "8,1^") WHERE npcspecialattks LIKE BINARY '%L%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "9,1^") WHERE npcspecialattks LIKE BINARY '%b%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "10,1^") WHERE npcspecialattks LIKE BINARY '%m%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "11,1^") WHERE npcspecialattks LIKE BINARY '%Y%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "12,1^") WHERE npcspecialattks LIKE BINARY '%U%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "13,1^") WHERE npcspecialattks LIKE BINARY '%M%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "14,1^") WHERE npcspecialattks LIKE BINARY '%C%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "15,1^") WHERE npcspecialattks LIKE BINARY '%N%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "16,1^") WHERE npcspecialattks LIKE BINARY '%I%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "17,1^") WHERE npcspecialattks LIKE BINARY '%D%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "18,1^") WHERE npcspecialattks LIKE BINARY '%K%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "19,1^") WHERE npcspecialattks LIKE BINARY '%A%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "20,1^") WHERE npcspecialattks LIKE BINARY '%B%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "21,1^") WHERE npcspecialattks LIKE BINARY '%f%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "22,1^") WHERE npcspecialattks LIKE BINARY '%O%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "23,1^") WHERE npcspecialattks LIKE BINARY '%W%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "24,1^") WHERE npcspecialattks LIKE BINARY '%H%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "25,1^") WHERE npcspecialattks LIKE BINARY '%G%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "26,1^") WHERE npcspecialattks LIKE BINARY '%g%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "27,1^") WHERE npcspecialattks LIKE BINARY '%d%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "28,1^") WHERE npcspecialattks LIKE BINARY '%i%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "29,1^") WHERE npcspecialattks LIKE BINARY '%t%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "30,1^") WHERE npcspecialattks LIKE BINARY '%n%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "31,1^") WHERE npcspecialattks LIKE BINARY '%p%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "32,1^") WHERE npcspecialattks LIKE BINARY '%J%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "33,1^") WHERE npcspecialattks LIKE BINARY '%j%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "34,1^") WHERE npcspecialattks LIKE BINARY '%o%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "35,1^") WHERE npcspecialattks LIKE BINARY '%Z%';
UPDATE npc_types SET special_abilities = TRIM(TRAILING '^' FROM special_abilities);

ALTER TABLE `npc_types` DROP COLUMN `npcspecialattks`;

[Mortykins]

Appreciate the quick response, I added the old field into the database, but there is not any entries into it. (All the data in that column is gone) I have the old npcspecialattks in a backup, isn't there a way for me to go in and remove that column by itself, then import it to the live database, then run that script? Apologies for the confusion on my part I am pretty rusty still with things. The reason I don't want to go back to the backup and do the script you listed is because we have made literally hundreds of other changes to those NPC's since we upgraded to latest build making tiers 1-3 easier so I would hate to lose all that work.

Mort

[Akkadius]

Appreciate the quick response, I added the old field into the database, but there is not any entries into it. (All the data in that column is gone) I have the old npcspecialattks in a backup, isn't there a way for me to go in and remove that column by itself, then import it to the live database, then run that script? Apologies for the confusion on my part I am pretty rusty still with things.

Mort

I would do a little bit of excel work by restoring the data from the old table, taking the old data and putting it into the live working table by pushing the NPC ID and the old NPC special attacks field in two different cells.

Then I'd do a concatenate generation based off of that data:

UPDATE `npc_types` SET npcspecialattks = X WHERE `id` = Y;

This would take me a matter of minutes.

Once I've restored this old data running side by side in the live table I would then run the conversion manually:

UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "1,1^") WHERE npcspecialattks LIKE BINARY '%S%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "2,1^") WHERE npcspecialattks LIKE BINARY '%E%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "3,1^") WHERE npcspecialattks LIKE BINARY '%R%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "4,1^") WHERE npcspecialattks LIKE BINARY '%r%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "5,1^") WHERE npcspecialattks LIKE BINARY '%F%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "6,1^") WHERE npcspecialattks LIKE BINARY '%T%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "7,1^") WHERE npcspecialattks LIKE BINARY '%Q%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "8,1^") WHERE npcspecialattks LIKE BINARY '%L%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "9,1^") WHERE npcspecialattks LIKE BINARY '%b%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "10,1^") WHERE npcspecialattks LIKE BINARY '%m%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "11,1^") WHERE npcspecialattks LIKE BINARY '%Y%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "12,1^") WHERE npcspecialattks LIKE BINARY '%U%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "13,1^") WHERE npcspecialattks LIKE BINARY '%M%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "14,1^") WHERE npcspecialattks LIKE BINARY '%C%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "15,1^") WHERE npcspecialattks LIKE BINARY '%N%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "16,1^") WHERE npcspecialattks LIKE BINARY '%I%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "17,1^") WHERE npcspecialattks LIKE BINARY '%D%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "18,1^") WHERE npcspecialattks LIKE BINARY '%K%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "19,1^") WHERE npcspecialattks LIKE BINARY '%A%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "20,1^") WHERE npcspecialattks LIKE BINARY '%B%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "21,1^") WHERE npcspecialattks LIKE BINARY '%f%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "22,1^") WHERE npcspecialattks LIKE BINARY '%O%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "23,1^") WHERE npcspecialattks LIKE BINARY '%W%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "24,1^") WHERE npcspecialattks LIKE BINARY '%H%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "25,1^") WHERE npcspecialattks LIKE BINARY '%G%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "26,1^") WHERE npcspecialattks LIKE BINARY '%g%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "27,1^") WHERE npcspecialattks LIKE BINARY '%d%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "28,1^") WHERE npcspecialattks LIKE BINARY '%i%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "29,1^") WHERE npcspecialattks LIKE BINARY '%t%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "30,1^") WHERE npcspecialattks LIKE BINARY '%n%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "31,1^") WHERE npcspecialattks LIKE BINARY '%p%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "32,1^") WHERE npcspecialattks LIKE BINARY '%J%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "33,1^") WHERE npcspecialattks LIKE BINARY '%j%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "34,1^") WHERE npcspecialattks LIKE BINARY '%o%';
UPDATE npc_types SET special_abilities = CONCAT(special_abilities, "35,1^") WHERE npcspecialattks LIKE BINARY '%Z%';
UPDATE npc_types SET special_abilities = TRIM(TRAILING '^' FROM special_abilities);

At this point I once you review that the new column looks good you are safe to drop the old.

[Mortykins]

Would you like to just Teamviewer in and assist me , if you have time that is?

Mort

[Akkadius]

Would you like to just Teamviewer in and assist me , if you have time that is?

Mort

Shoot me the TV info in a PM and I'll look shortly.

[Mortykins]

Using skills , that i have never seen , almost ninja like Akkadius was able to kick some ass and fix our issue.

"I PROMISE TO NEVER TALK SHIT ABOUT HIM AGAIN!" -Jordan Mortenson 7/26/15

Morty !