[Release] Usefull Database Queries [Part 2]

[latruwski]

well to continue my last topic with usefull DB queries i made this one...
Part 1 is here: http://www.mmowned.com/forums/emulat...e-queries.html
NOTE: the query to delete old unused accs/chars is in the first part !!!

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

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

change the buyprice to whatever you want and the entry too to the entry you chosen for your custom vendor


Maintenance Queries thx to NCDB:

-- Delete vendors with none existing creatures/items.
DELETE FROM `vendors` WHERE `entry` not in (SELECT `entry` from `creature_proto`);
DELETE FROM `vendors` WHERE `item` not in (SELECT `entry` from `items`);

-- Delete waypoints with none existing spawns
DELETE FROM `creature_waypoints` WHERE `spawnid` not in (SELECT `id` from `creature_spawns`);

-- Delete trainers with none existing creatures ** New table **
DELETE FROM `trainer_spells` WHERE `entry` not in (SELECT `entry` from `creature_proto`);
DELETE FROM `trainer_defs` WHERE `entry` not in (SELECT `entry` from `creature_proto`);

-- Delete quest starter/finisher where quests doesn't exist
DELETE FROM `creature_quest_starter` WHERE `quest` not in (SELECT `entry` from `quests`);
DELETE FROM `creature_quest_finisher` WHERE `quest` not in (SELECT `entry` from `quests`);
DELETE FROM `gameobject_quest_starter` WHERE `quest` not in (SELECT `entry` from `quests`);
DELETE FROM `gameobject_quest_finisher` WHERE `quest` not in (SELECT `entry` from `quests`);

-- Delete quest starter/finisher where creature/gameobject doesn't exist
DELETE FROM `creature_quest_starter` WHERE `id` not in (SELECT `entry` from `creature_proto`);
DELETE FROM `creature_quest_finisher` WHERE `id` not in (SELECT `entry` from `creature_proto`);
DELETE FROM `gameobject_quest_starter` WHERE `id` not in (SELECT `entry` from `gameobject_names`);
DELETE FROM `gameobject_quest_finisher` WHERE `id` not in (SELECT `entry` from `gameobject_names`);

-- Delete loots that itemid not exists.
DELETE FROM `itemloot` WHERE `itemid` not in (SELECT `entry` from `items`);
DELETE FROM `objectloot` WHERE `itemid` not in (SELECT `entry` from `items`);
DELETE FROM `creatureloot` WHERE `itemid` not in (SELECT `entry` from `items`);
DELETE FROM `pickpocketingloot` WHERE `itemid` not in (SELECT `entry` from `items`);
DELETE FROM `fishingloot` WHERE `itemid` not in (SELECT `entry` from `items`);
DELETE FROM `skinningloot` WHERE `itemid` not in (SELECT `entry` from `items`);
DELETE FROM `prospectingloot` WHERE `itemid` not in (SELECT `entry` from `items`);

-- Delete loots that entryid not exists.
DELETE FROM `itemloot` WHERE `entryid` not in (SELECT `entry` from `items`);
DELETE FROM `prospectingloot` WHERE `entryid` not in (SELECT `entry` from `items`);
DELETE FROM `fishingloot` WHERE `entryid` not in (SELECT `Zone` from `fishing`);
DELETE FROM `objectloot` WHERE `entryid` not in (SELECT `entry` from `gameobject_names`);
DELETE FROM `creatureloot` WHERE `entryid` not in (SELECT `entry` from `creature_proto`);
DELETE FROM `pickpocketingloot` WHERE `entryid` not in (SELECT `entry` from `creature_proto`);
DELETE FROM `skinningloot` WHERE `entryid` not in (SELECT `entry` from `creature_proto`);

Query to order you tables again thx to NCDB:

ALTER TABLE `areatriggers` ORDER BY `entry` ASC;
ALTER TABLE `creature_spawns` ORDER BY `id` ASC;
ALTER TABLE `creature_quest_starter` ORDER BY `id` ASC, `quest` ASC;
ALTER TABLE `creature_proto` ORDER BY `entry` ASC;
ALTER TABLE `creature_names` ORDER BY `entry` ASC;
ALTER TABLE `gameobject_names` ORDER BY `entry` ASC;
ALTER TABLE `gameobject_quest_finisher` ORDER BY `id` ASC, `quest` ASC;
ALTER TABLE `gameobject_quest_starter` ORDER BY `id` ASC, `quest` ASC;
ALTER TABLE `gameobject_spawns` ORDER BY `id` ASC;
ALTER TABLE `itempages` ORDER BY `entry` ASC;
ALTER TABLE `items` ORDER BY `entry` ASC;
ALTER TABLE `npc_text` ORDER BY `entry` ASC;
ALTER TABLE `playercreateinfo_spells` ORDER BY `indexid` ASC;
ALTER TABLE `quests` ORDER BY `entry` ASC;
ALTER TABLE `recall` ORDER BY `name` ASC;
ALTER TABLE `vendors` ORDER BY `vendorGUID` ASC, `entry` ASC;
ALTER TABLE `weather` ORDER BY `zoneId` ASC;

Query to change playercreateinfo:

INSERT INTO `playercreateinfo` (`Index`, `race`, `factiontemplate`, `class`, `mapID`, `zoneID`, `positionX`, `positionY`, 
`positionZ`, `displayID`, `BaseStrength`, `BaseAgility`, `BaseStamina`, `BaseIntellect`, `BaseSpirit`, 
`BaseHealth`, `BaseMana`, `BaseRage`, `BaseFocus`, `BaseEnergy`, `attackpower`, `mindmg`, `maxdmg`) VALUES
(58, 10, 1610, 1, 530, 3430, -6257.02, -13931.2, 33.4026, 15476, 20, 25, 50, 20, 20, 50, 0, 1000, 0, 0, 23, 4, 5);

just mod values to what you wnt it to be

Removing Costs and Conditions of obtaining items by xeneth, give him rep by pressing here:+Rep
UPDATE `items` SET ItemExtendedCost = 0 WHERE `entry` BETWEEN '1' and '40000';
note: once you changed the itemextendedcost, you can not change it back to original by 1 query!
info on that query:

                              UPDATE "items" = Table that the query is being executed in. (replace ascent with whatever your world database is named)
 
WHERE "entry" = Column 
 
BETWEEN '1' and '40000'; = Lines to be Edited according to selected column.

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

? = Item Extended Cost = 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)
additional credit notes to his query:

                              Special thanks goes to
 
Mafiaboy
 
Julianx
 
Illadin1
 
Gastric

here is something i just posted to help someone:
to make a race/class start with a skill you wnt and the amount of skill you wnt
use this query:

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 wnt all Blood elf paladins to start with 300 riding skill the query would be:

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

and voila done all blood elf paladins will start with 300 riding skill now

Update: 25-03-08

update characters set taximask = '4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295' where name like '%Latruwski%';

Change latruwski to a other character name to give it all taxi nodes...

update characters set taximask = '4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295';

This query gives all texi nodes to all characters...

update characters set taximask = '4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295' where acct = (select acct from accounts where login like '%Latruwski%');

Change latruwski to an account name to unlock all flight nodes on all characters on that account...


more to come

link to part 1 of this post:
http://www.mmowned.com/forums/emulat...e-queries.html

grtz

[chaoticd35]

I really like this, Especially the set buy price code you got..Got to spread some rep before I can give it to you again but this is nice.

[Illidan1]

Nice mate, keep them coming aye

[Xeneth]

this is pretty useful, you could add it to the list

http://www.mmowned.com/forums/emulat...tml#post496164

[latruwski]

this is pretty useful, you could add it to the list

http://www.mmowned.com/forums/emulat...tml#post496164

updated with new query and yours !
remember: you can submit queries to, i'll add them

grtz

[latruwski]

updated again with new query i just posted to help someone

grtz

[Xeneth]

Thanks for adding mine ^_^

[latruwski]

Thanks for adding mine ^_^

np people should share more queries

grtz

[latruwski]

Updated with queries to unlock all flight nodes...

enjoy

[klorox]

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

Just says processing for a long time, then if i click stop it says lost connection, other querys dont do this though.

[latruwski]

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

Just says processing for a long time, then if i click stop it says lost connection, other querys dont do this though.

worked perfectly for me... are u sure you used a existing vendorID ?

[Lich King]

Great queries latruwski

[klorox]

The id for my vendor is 70003, its a S3 vendor. So yeah im pretty sure its all correct. I used the UPDATE `items` SET buyprice = 500000 WHERE `entry` BETWEEN '1' and '40000'; and that works fine

[Lich King]

Well if you want everything on your server free, then do this:

UPDATE `items` SET buyprice = 0 WHERE `entry` BETWEEN '1' and '99999999';

[klorox]

Well never mind iv managed to figure out another way