This is an old tutorial i made years ago, it might be updated if requested!
You can request a query and I'll take my time and create it for you.
Play sound on quest accept.
Remove the "Level 80" Mail (Could be a nice feature for Instant 80 realms.)
Remove all spellcosts in trainers and remove all itemcosts in vendors.
Disable a spell in a certain zone.
Set the same starting area for all races/classes.
Explanation:
CONCAT merges strings together
SUBSTRING gets a part of a string, in this case the name of the item (example: the Claymore part)
LENGTH calculates the length of the string. I used it there so you can change the string to take away from the beginning of the name.
So, "Relentless " is erased and "Starter " is placed in it's place for all items with name like "Relentless Gladiator's "..
This sql will update your item sets stats by specific set name.
So if you want all wrathful items updated 15% stats more this is how, but if you want some other Set just replace wrathful.
and if u want more % just change number 1.15 if 25% = 1.25 etc
You can request a query and I'll take my time and create it for you.
Play sound on quest accept.
Code:
SET @[COLOR="#FF0000"]Sound :=[COLOR="#00FF00"]1234[/COLOR][/COLOR]; -- Replace with desired sound ID you wish you play.
SET @[COLOR="#FF0000"]Start :=[COLOR="#00FF00"]5555[/COLOR][/COLOR]; -- Replace with Unique ID for end Script, set this to whatever you want.
SET @[COLOR="#FF0000"]Quest :=[COLOR="#00FF00"]25[/COLOR][/COLOR]; -- Replace with Quest ID wished to link sound to.
INSERT INTO `quest_start_scripts` VALUES (@Start, 1, 16, @Sound, 1, 0, 0, 0, 0, 0);
UPDATE `quest_template` SET startscript=@Start WHERE id=@Quest;
Remove the "Level 80" Mail (Could be a nice feature for Instant 80 realms.)
Code:
DELETE FROM `achievement_reward` WHERE `entry` = "13";
Remove all spellcosts in trainers and remove all itemcosts in vendors.
Code:
UPDATE `npc_trainer` SET `spellcost` = 0
UPDATE `item_template` SET `buycost` = 0
Disable a spell in a certain zone.
Code:
SET @[COLOR="#FF0000"]entry :=[COLOR="#00FF00"]1234[/COLOR][/COLOR]; -- Replace with the Spell ID.
SET @[COLOR="#FF0000"]params_1 :=[COLOR="#00FF00"]5555[/COLOR][/COLOR]; -- Replace with the Zone ID.
SET @[COLOR="#FF0000"]comment :=[COLOR="#00FF00"]Spell Disable[/COLOR][/COLOR]; -- Replace with any comment.
INSERT INTO `disables` VALUES (3, @entry, 49, 0, @params_1, @comment);
Set the same starting area for all races/classes.
Code:
SET @[COLOR="#FF0000"]map :=[COLOR="#00FF00"]1234[/COLOR][/COLOR]; -- Replace with the Map ID.
SET @[COLOR="#FF0000"]zone :=[COLOR="#00FF00"]1234[/COLOR][/COLOR]; -- Replace with the Zone ID.
SET @[COLOR="#FF0000"]position_x :=[COLOR="#00FF00"]1234[/COLOR][/COLOR]; -- Replace with Position_x.
SET @[COLOR="#FF0000"]position_y :=[COLOR="#00FF00"]1234[/COLOR][/COLOR]; -- Replace with the Position_y.
SET @[COLOR="#FF0000"]position_z :=[COLOR="#00FF00"]1234[/COLOR][/COLOR]; -- Replace with the Position_z.
SET @[COLOR="#FF0000"]orientation :=[COLOR="#00FF00"]1234[/COLOR][/COLOR]; -- Replace with Orientation.
UPDATE `playercreateinfo` SET map=@map AND zone=@zone AND position_x=@position_x AND position_y=@position_y AND position_z=@position_z AND orientation=@orientation WHERE race>=1 AND race<=11;
Set the required level on the item you want to change.
Change Minimum and maximum damage.Code:UPDATE `Item_template` SET requiredLevel = [COLOR="#FF0000"]required level[/COLOR] WHERE `Name` LIKE '%[COLOR="#FF0000"]Name on item here[/COLOR]%';
Choose what class the item should be available for.Code:UPDATE `Item_template` SET dmg_min1 =[COLOR="#FF0000"] Min DMG[/COLOR] WHERE `Name` LIKE '%[COLOR="#FF0000"]Name on item here[/COLOR]%'; UPDATE `Item_template` SET dmg_max1 = [COLOR="#FF0000"]Max DMG[/COLOR] WHERE `Name` LIKE '%[COLOR="#FF0000"]Name on item here[/COLOR]%';
Code:UPDATE `Item_template` SET AllowableClass = [COLOR="#FF0000"]Class id here[/COLOR] WHERE `entry` LIKE '%[COLOR="#FF0000"]Entry id on item here[/COLOR]%';
Explanation:
CONCAT merges strings together
SUBSTRING gets a part of a string, in this case the name of the item (example: the Claymore part)
LENGTH calculates the length of the string. I used it there so you can change the string to take away from the beginning of the name.
So, "Relentless " is erased and "Starter " is placed in it's place for all items with name like "Relentless Gladiator's "..
Code:
UPDATE item_template SET name = CONCAT("Starter ", SUBSTRING(name, LENGTH("Relentless ")+1)) WHERE name like "Relentless Gladiator's %";
This sql will update your item sets stats by specific set name.
So if you want all wrathful items updated 15% stats more this is how, but if you want some other Set just replace wrathful.
and if u want more % just change number 1.15 if 25% = 1.25 etc
Code:
UPDATE `Item_template` SET stat_value1 = (stat_value1 * 1.15) WHERE `Name` LIKE '%Wrathful%';
UPDATE `Item_template` SET stat_value2 = (stat_value2 * 1.15) WHERE `Name` LIKE '%Wrathful%';
UPDATE `Item_template` SET stat_value3 = (stat_value3 * 1.15) WHERE `Name` LIKE '%Wrathful%';
UPDATE `Item_template` SET stat_value4 = (stat_value4 * 1.15) WHERE `Name` LIKE '%Wrathful%';
UPDATE `Item_template` SET stat_value5 = (stat_value5 * 1.15) WHERE `Name` LIKE '%Wrathful%';
UPDATE `Item_template` SET stat_value6 = (stat_value6 * 1.15) WHERE `Name` LIKE '%Wrathful%';
UPDATE `Item_template` SET stat_value7 = (stat_value7 * 1.15) WHERE `Name` LIKE '%Wrathful%';
UPDATE `Item_template` SET stat_value8 = (stat_value8 * 1.15) WHERE `Name` LIKE '%Wrathful%';
COPY an existing display ID
Code:SET @myItem = '96000'; /* entry of your item */ SET @copyItem = 'Hearthstone'; /* name of item to copy */ SET @myValue = (SELECT displayid FROM item_template WHERE NAME LIKE @copyItem); UPDATE item_template SET displayid=@myValue WHERE entry=@myItem;
Since I requested a code I'd love to contribute with some that I used and proved very handy ^^
1. Make a simple NPC
Code:SET @Entry :=50003; SET @ModelID :=27436; SET @Name :='Name Here'; SET @Subname :='Subname Here'; SET @NPCFLAG :=2; -- 1 is gossip / scripted npc's, 4224 is vendor DELETE FROM `creature_template` WHERE `entry`=@Entry; INSERT INTO `creature_template` (`entry`, `difficulty_entry_1`, `difficulty_entry_2`, `difficulty_entry_3`, `KillCredit1`, `KillCredit2`, `modelid1`, `modelid2`, `modelid3`, `modelid4`, `name`, `subname`, `IconName`, `gossip_menu_id`, `minlevel`, `maxlevel`, `exp`, `faction_A`, `faction_H`, `npcflag`, `speed_walk`, `speed_run`, `scale`, `rank`, `mindmg`, `maxdmg`, `dmgschool`, `attackpower`, `dmg_multiplier`, `baseattacktime`, `rangeattacktime`, `unit_class`, `unit_flags`, `unit_flags2`, `dynamicflags`, `family`, `trainer_type`, `trainer_spell`, `trainer_class`, `trainer_race`, `minrangedmg`, `maxrangedmg`, `rangedattackpower`, `type`, `type_flags`, `lootid`, `pickpocketloot`, `skinloot`, `resistance1`, `resistance2`, `resistance3`, `resistance4`, `resistance5`, `resistance6`, `spell1`, `spell2`, `spell3`, `spell4`, `spell5`, `spell6`, `spell7`, `spell8`, `PetSpellDataId`, `VehicleId`, `mingold`, `maxgold`, `AIName`, `MovementType`, `InhabitType`, `HoverHeight`, `Health_mod`, `Mana_mod`, `Armor_mod`, `RacialLeader`, `questItem1`, `questItem2`, `questItem3`, `questItem4`, `questItem5`, `questItem6`, `movementId`, `RegenHealth`, `mechanic_immune_mask`, `flags_extra`, `ScriptName`, `WDBVerified`) VALUES (@Entry, 0, 0, 0, 0, 0, @ModelID, 0, 0, 0, @Name, @Subname, '', 0, 80, 80, 2, 35, 35, @NPCFLAG, 1, 1.14286, 1, 3, 10, 10, 0, 0, 1, 1000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '', 0, 3, 1, 10000, 0, 100, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, '', 1);
Insert all weapons with a specific item level into the npc_vendor table:
Code:SET @Entry :=60047; SET @iLvL :='232'; INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `ExtendedCost`) SELECT @Entry, 0, entry, 0, 0, 0 FROM item_template WHERE ItemLevel=@iLvL and (class='2' and name!='Furious Gladiator');
This one is for off pieces
Code:SET @Entry :=50003; SET @iLvL :='219'; INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `ExtendedCost`) SELECT @Entry, 0, entry, 0, 0, 0 FROM item_template WHERE ItemLevel=@iLvL and (subclass='7' or subclass='9' or subclass='8' or subclass='10' and InventoryType='2' or InventoryType='6' or InventoryType='8' or InventoryType='9' or InventoryType='11' or InventoryType='12' or InventoryType='16');
and this one is to add all shields with a specific name to a vendor
Code:SET @Entry :=500123; INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `ExtendedCost`) SELECT @Entry, 0, entry, 0, 0, 0 FROM item_template WHERE name like "%Wrathful Gladiator's%" and (class='4' and subclass='6' and sheath='4');
and the last one I have right now is meant to be created for a multivendor, it adds all the main pieces into different entrys:
Code:INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `ExtendedCost`) SELECT 60046, 0, entry, 0, 0, 0 FROM item_template WHERE ItemLevel='219' AND InventoryType='1'; INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `ExtendedCost`) SELECT 60047, 0, entry, 0, 0, 0 FROM item_template WHERE ItemLevel='219' AND InventoryType='3'; INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `ExtendedCost`) SELECT 60048, 0, entry, 0, 0, 0 FROM item_template WHERE ItemLevel='219' AND InventoryType='5'; INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `ExtendedCost`) SELECT 60049, 0, entry, 0, 0, 0 FROM item_template WHERE ItemLevel='219' AND InventoryType='7'; INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `ExtendedCost`) SELECT 60050, 0, entry, 0, 0, 0 FROM item_template WHERE ItemLevel='219' AND InventoryType='10';