[USEFUL] MYSQL MOST USED AND MOST NEEDED SQL QUERIES

You made a vendor? and want all items that the vendor sells to be sold at 1 price? use this query to do so:

Quote:

UPDATE items SET buyprice = '13370000 WHERE entry IN (SELECT item FROM vendors WHERE entry = 50000);

FOR FUNSERVERS AND PPL THAT WANT TO REMOVE HONOR / BADGES REQ, CURRENCY AND FACTION REQUIREMENTS.

Quote:

UPDATE `items` SET ItemExtendedCost = 0 WHERE `entry` BETWEEN '1' and '40000';

what can be used instead of itemextendedcost:
SET ? = 0

? = ItemExtendedCost = Removes Arena point requirement/Honor Point Requirement

? = ArenaRankRequirement = Disables Arena Rank Requirement

? = RequiredFaction = Disables Faction Requirment (Revered, Exalted, Et'cetra)

? = RequiredFactionStanding = Disables Required Faction Standing (Thrallmar, Scryers, Et'cetra)

? = buyprice = How much the item will cost if in a shop

? = sellprice = How much the item will sell for (I set to 1 for every
item in the game, So when you trash items you'd normally have to type
in "Delete" for, You can just sell to vendor, It's faster and your
players will thank you for it)



Delete all accounts which have not been online for some period of time

before runign this query change ACCTDATABASE to the name of your databse

Quote:

DELETE FROM `ACCTDATABASE`.`accounts` WHERE `lastlogin` < '2007-04-15 00:00:00';

to make a race/class start with a skill you want and the amount of skill you want
use this query:

Quote:

INSERT INTO `playercreateinfo_skills` VALUES('2','762', '300', '300');

First number is IndexID that is the index number of the playercreateinfo table

more info on this: well in total you have 52 possible class/race
combinations all of them are in playercreateinfo table... so you need
to check that table...
if you wnt all players to start with maxed skill then just use this
query with all indexIDs if you wnt a specific race/class to start with
maxed skill then check playercreateinfo for class/race and pick the
index of the one you want to use... for example

Blood elf Paladin with maxed out riding skill (in my DB):
playercreateinfo search for raceID: 10
classID: 2
corresponding index: 50

Second number = skillID
3th and 4th number = skill/maxskill
example 300riding skill would be:
762,300,300

so if i want all Blood elf paladins to start with 300 riding skill the query would be:

Quote:

INSERT INTO `playercreateinfo_skills` VALUES('50','762', '300', '300');

-------------------------------------------------------

SELECT Queries are a great way to detect bugs in the database, Here we will make a list.

Creatures

Code:

Quote:

select * from creature_proto where maxhealth < minhealth ;

Code:


Quote:

select * from creature_proto where maxlevel < minlevel; select * from creature_proto where maxdamage < mindamage';

A maximum value should not be less than the minimum.

Code:

Quote:

select * from creature_names where displayid = 0;

List of all the mobs without model.
Code:

Quote:

SELECT `entry` FROM `creature_proto` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_spawns`);

List of all creatures that exist in creature_proto but not spawned in the world.
Code:

Quote:

SELECT `entry` FROM `creature_spawns` WHERE `entry` NOT IN (SELECT `entry` FROM `creature_proto`);

List of all the creatures that are spawned in the world but not exist in proto table.
Code:

Quote:

SELECT * FROM creature_waypoints WHERE NOT creatureid IN(SELECT id FROM creature_spawns);

List of none Existing waypoints
Items
Code:

Quote:

select * from `items` where `BuyPrice`<`SellPrice`;

List of items that have bigger sell price then buy price.



Quests

Code:
Quote:

drop table if exists tmp;

Code:


Quote:

CREATE TEMPORARY TABLE tmp ENGINE=memory SELECT quest FROM creature_quest_finisher; INSERT INTO tmp SELECT quest FROM gameobject_quest_starter; INSERT INTO tmp SELECT quest FROM gameobject_quest_finisher; INSERT INTO tmp SELECT quest_id FROM items; alter table tmp add index quest(quest); drop table if exists orphans; CREATE TABLE orphans ENGINE=myisam SELECT entry, title FROM quests WHERE NOT entry in (select * from tmp); ALTER TABLE orphans ADD INDEX (entry), ADD PRIMARY KEY (entry);

List of quests without quest creature/gameobject/item starter/finisher.("Orphan quests").


Misc
Code:

Quote:

SELECT * FROM gameobject_spawns WHERE NOT entry IN (SELECT entry FROM gameobject_names);

List of gameobjects that spawned but not exists.
Code:

Quote:

SELECT * FROM gameobject_names WHERE displayId=0;

List of gameobjects without model

yeah i used the top few over on ragezone, one thing to point out when you modify the gold is its counted as copper just like when you use the command .modify gold value ingame.

can someone please help me, rouslan can you explain to me in detail the steps to change this, i am very very new to the private esrver and i want me and my friends to not have to worry about the ranks and cost and all that, please helpme