SQL - RageGate/ServerStuff GitHub Wiki

##SQL Useful Queries:

###Default template to change an value of an Table_Row of an ID OR Name

UPDATE `Database_table` SET `Table_Row` = Value  WHERE `entry` LIKE 'ID_Here';
UPDATE `Database_table` SET `Table_Row` = Value  WHERE `Name` LIKE '%Name_Here%';

example:
UPDATE `Item_template` SET dmg_min1 = 1234 WHERE `entry` LIKE 'ID_Here';
UPDATE `Item_template` SET dmg_min1 = 1234 WHERE `Name` LIKE '%Name_Here%';   

###Remove the "Level 80" Mail

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;

###Add custom entry for an (instance)map

INSERT INTO instance_template` VALUES ('MAPID', '', '1');

###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 starting zone 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 Starting zone of Horde Races

SET
@MAP := 'MAP',
@ZONE := 'ZONE',
@X := 'X-Cordinate',
@Y := 'Y-Cordinate',
@Z := 'Z-Cordinate',
@O := 'Orientation';

UPDATE playercreateinfo SET map=@MAP, zone=@ZONE, position_X=@X, position_Y=@Y, position_Z=@Z, orientation=@O WHERE race IN(2, 5, 6, 8, 10);

###Set Starting zone of Alliance Races

SET
@MAP := 'MAP',
@ZONE := 'ZONE',
@X := 'X-Cordinate',
@Y := 'Y-Cordinate',
@Z := 'Z-Cordinate',
@O := 'Orientation';

UPDATE playercreateinfo SET map=@MAP, zone=@ZONE, position_X=@X, position_Y=@Y, position_Z=@Z, orientation=@O WHERE race IN(1, 3, 4, 7, 11);

###Create teleport Portals (Object)

@GOB_Name := ID_Here,
@GOB_Name := Name_Here,
@GOB_Display := DisplayID_Here,
@Event_scriptID := 33333,
@MAP := 0,
@X := -1825.442139,
@Y := -4222.890625,
@Z := 3.861495,
@O := 4.181533;

INSERT INTO `gameobject_template` (`entry`, `type`, `displayId`, `name`, `size`, `data2`) VALUES (@GOB_Entry, 10, @GOB_Display, @GOB_Name, 1, @Event_scriptID);
REPLACE INTO `event_scripts` (`id`, `command`, `datalong`, `x`, `y`, `z`, `o`) VALUES (@Event_scriptID, 6, @MAP, @X, @Y, @Z, @O);