[Trinitycore] Useful SQL queries

Zafire

https://zafirehd.deviantart.com
Admin
Designer
Senior User
245
2013
22
Location
Siberia
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.
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.
Code:
UPDATE `Item_template` SET requiredLevel = [COLOR="#FF0000"]required level[/COLOR] WHERE `Name` LIKE '%[COLOR="#FF0000"]Name on item here[/COLOR]%';
Change Minimum and maximum damage.
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]%';
Choose what class the item should be available for.
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';
 

Xavier

Verified Member
10
2019
0
I was looking to increasing the amount of loot dropped for items that that can drop multiples (ore, leather, meat etc), wonder if the query below would do that?

UPDATE 'Creature_loot_template' SET 'maxcount' = 'maxcount' * 2 WHERE 'maxcount' > 1
 
Top