Jump to content

WoW Source uses cookies. Read the Privacy Policy for more info. To remove this message, please click the button to the right:    I accept the use of cookies

Toggle shoutbox
General Chat Box
Open the Shoutbox in a popup

@  Kalimdor : (29 May 2018 - 08:10 PM) we are waiting the new patch of wow guys
@  Kalimdor : (30 March 2018 - 12:40 AM) hello
@  Rusas : (30 March 2018 - 12:08 AM) Hi )
@  Kalimdor : (19 March 2018 - 07:43 AM) Hey guys your all invited to our new private server of legion, patch 7.3.5 http://wowargus.com/ before start playing make sure you have an account and have the right client but for more info read this please http://wowargus.com/news/1 if you find bug inside your free to post them here http://wowargus.com/bugtracker and for pvp statistics here http://wowargus.com/pvp
@  Kalimdor : (14 March 2018 - 04:58 PM) Who needs me just use discord and add Ozeneu#7067 or skype geany_100
@  Kalimdor : (09 March 2018 - 05:28 PM) hi check your gm level and also check the command for level what gm rank required
@  gmacken : (07 March 2018 - 10:17 AM) silly question but a new to this, got server 4.3.4 and i have set admin account etc. It does not appear to recognise command for levelling up ?
@  Kalimdor : (11 February 2018 - 05:32 PM) look guys there is no team so i wontwork alone any more but if any of you wish to work with me and you have skills in c++ and sql, send me a reply or a PM
@  Kalimdor : (25 January 2018 - 07:47 AM) hey guys my youtube channel it is https://www.youtube....w_as=subscriber
@  remamakiller : (18 December 2017 - 01:40 PM) testear quest , agregar o buscar el phaseid que se necesita para que se puedan completar
@  remamakiller : (18 December 2017 - 01:38 PM) Me ofresco desinteresadamente , para arreglar quest de salida de personajes
Mas no se
@  Kalimdor : (06 December 2017 - 09:48 PM) guess what? the legion update is working well i have add lots of missing objects and mobs but this will work faster if i have a sniffer 7.2 and if i have people who work with me to this project because for 1 person is very hard
@  Kalimdor : (02 November 2017 - 04:20 PM) hi guys good news about legion it will be public source with updates we start work in legion patch 7.2.5 me and 2 friends the source will be public and updates, if any of you wish to join give me a PM or a reply, the address for download it is https://github.com/WoWSource
@  Kalimdor : (02 November 2017 - 01:02 PM) english please
@  samaelsacred : (01 November 2017 - 02:10 AM) alguien vende un source 4.3.4?
@  Kalimdor : (14 October 2017 - 02:17 PM) guys some time when register the activation is not instant coz i must accept or not the new members so this could take between 1 hour and a few days
@  Kalimdor : (20 September 2017 - 02:01 PM) por supuesto si 4.3.4 - 5.4.8
@  Dirkwein : (20 September 2017 - 05:33 AM) Kalimdor la 4.3.4 la siguieron actualizando para los vip?
@  Kalimdor : (12 September 2017 - 09:23 PM) 2 big updates was made today into 5.4.8 source check the github commits if your a vip
@  Kalimdor : (29 August 2017 - 07:57 PM) We have so far live 2 servers 2 patches like 4.3.4 and 5.4.8 you can visit and play in http://5.196.199.233/ and 5.4.8 http://pandemowow.com/ and we will start legion server in short time

Photo

Advanced SQL Queries


  • Please log in to reply
2 replies to this topic

#1 Kalimdor

Kalimdor

    The First

  • Administrators
  • 3,945 posts
  • 1610 thanks
  • LocationNorth Pole
  • Time Online: 48D 11H 30M 25S

User's Awards

                                

Posted 11 May 2013 - 05:32 AM

This is all for TrinityCore2, Enjoy.

Request a Query and I'll take my time and create it for you.
Play sound on quest accept.
SET @Sound :=1234; -- Replace with desired sound ID you wish you play.
SET @Start :=5555; -- Replace with Unique ID for end Script, set this to whatever you want.
SET @Quest :=25; -- 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.)

DELETE FROM `achievement_reward` WHERE `entry` = "13";

Remove all spellcosts in trainers and remove all itemcosts in vendors.

UPDATE `npc_trainer` SET `spellcost` = 0;

UPDATE `item_template` SET `buycost` = 0;

Disable a spell in a certain zone.

SET @entry :=1234; -- Replace with the Spell ID.
SET @params_1 :=5555; -- Replace with the Zone ID.
SET @comment :=Spell Disable; -- Replace with any comment.
INSERT INTO `disables` VALUES (3, @entry, 49, 0, @params_1, @comment);

Set the same starting area for all races/classes.

SET @map :=1234; -- Replace with the Map ID.
SET @zone :=1234; -- Replace with the Zone ID.
SET @position_x :=1234; -- Replace with Position_x.
SET @position_y :=1234; -- Replace with the Position_y.
SET @position_z :=1234; -- Replace with the Position_z.
SET @orientation :=1234; -- 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.
UPDATE `Item_template` SET requiredLevel = required level WHERE `Name` LIKE '%Name on item here%';

Change Minimum and maximum damage.
UPDATE `Item_template` SET dmg_min1 = Min DMG WHERE `Name` LIKE '%Name on item here%';
UPDATE `Item_template` SET dmg_max1 = Max DMG WHERE `Name` LIKE '%Name on item here%';

Choose what class the item should be available for.
UPDATE `Item_template` SET AllowableClass = Class id here WHERE `entry` LIKE '%Entry id on item here%';

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 "..
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

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


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
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:

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

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
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:

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';






original posted by Zafire


  • 2

facebook-icon.png  YouTube-icon.png

 

 

 


Thanked by 4 Members:
Graymane , naaru , conceptor , Germo

#2 aminonsori123

aminonsori123

    Beginer

  • Members
  • 1 posts
  • 0 thanks
  • Time Online: 1H 35M 2S

Posted 28 September 2013 - 08:59 PM

AwSome Job :)


  • 0

#3 naaru

naaru

    Member

  • Members
  • 12 posts
  • 1 thanks
  • LocationMatanzas, Cuba
  • Time Online: 1D 11H 50M 39S

Posted 19 June 2014 - 03:54 PM

nice, very helpfull!!!!


  • 0