Go Back   EQEmulator Home > EQEmulator Forums > Development > Development::Database/World Building

Development::Database/World Building World Building forum, dedicated to the EQEmu MySQL Database. Post partial/complete databases for spawns, items, etc.

Reply
 
Thread Tools Display Modes
  #1  
Old 04-07-2018, 01:23 PM
GRUMPY
Discordant
 
Join Date: Oct 2016
Posts: 445
Default SQL Tips - Mass Edit

This is just a couple handy tips for those who are just getting started in
learning SQL and these examples may help you understand a bit better.
These are simple examples but SQL is great for doing mass editing in the
database.
In a nutshell, what you're doing is updating a value under a specific field
in one particular table, but this can be done with a mass query. I will use
the items table for example. But basic format/formula goes like this.
UPDATE tablenamehere SET fieldnamehere = valuehere;
So, for example, if you wanted to set all items in the database to be All/All
with the No Drop and Temporary tags removed, you could put all four of
these lines into one query:

UPDATE items SET classes = 65535;
UPDATE items SET races = 65535;
UPDATE items SET nodrop = 1;
UPDATE items SET norent = 1;


Just KEEP IN MIND, a value of 0, does not necessarily mean "disable" or
remove a flag, but it can be different for some fields. Like the following:

UPDATE items SET nodrop = 1; (will remove the No Drop flag from an item).
UPDATE items SET attuneable = 0; (will remove the attuneable flag).

BUT, if you wanted to make ALL items, EXCEPT the spells, tombs and songs
to be ALL/ALL, then you could exclude those items. Since spells, tombs and
songs have a "scrolleffect" to them, with a value greater than 0, you could
do this :

UPDATE items SET classes = 65535 WHERE scrolleffect > 0;
UPDATE items SET races = 65535 WHERE scrolleffect > 0;


NOTE: Each class and race has it's own value, but 65535 means ALL. There is
pages (and even class/race/deity calculators) in the Main Wiki.

Most of the basics are simple telling the query what you want it to do.
You want to enable all the mercs, by setting the appropriate class (71) ?
Keep in mind, you also need to enable mercs (true) in the rule_values table.
In this kind of query, you're setting the class of all NPC's in the database
with the last name of Mercenary Liaison.

UPDATE npc_types SET class = 71 WHERE lastname LIKE '%Mercenary Liaison';

If you've ever put an item on a custom merchant and set a price to it, some
items have a sellrate assigned to them. (price mod), so it will show up at
a higher price than you assigned. You can remove that price mod from all items
with this simple query:

UPDATE items SET sellrate = 1; (don't forget the ; on the end), but you can
put as many lines in a query, as you want. I recently wrote up a query file with 404
lines to it. Yes, a bit of typing, but it saved me lots of time in the end.
You may have to do some homework to find out what optional values you have
for various types of fields, etc. Hope this all helps a few people
Reply With Quote
  #2  
Old 04-08-2018, 07:03 AM
Kingly_Krab
Administrator
 
Join Date: May 2013
Location: United States
Posts: 1,589
Default

I would recommend making a backup of your database before running any query that modifies your items this much. It’d be nearly impossible to determine which items had a requirement before if they all look the same and you have no reference.
Reply With Quote
  #3  
Old 04-08-2018, 09:05 AM
GRUMPY
Discordant
 
Join Date: Oct 2016
Posts: 445
Default

Quote:
Originally Posted by Kingly_Krab View Post
I would recommend making a backup of your database before running any query that modifies your items this much.
Oh definately Kingly, I agree, but something I like to do myself is backup individual tables
before I modify them. Sometimes, when I edit several tables in a day, or hours, if I wanted
to keep some work and discard one table, reverting back to original then I don't need to
worry about the rest not being there in a full database backup, which I usually do at the
end of the day. But I also backup my backups, haha.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

   

All times are GMT -4. The time now is 12:24 PM.


 

Everquest is a registered trademark of Daybreak Game Company LLC.
EQEmulator is not associated or affiliated in any way with Daybreak Game Company LLC.
Except where otherwise noted, this site is licensed under a Creative Commons License.
       
Powered by vBulletin®, Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Template by Bluepearl Design and vBulletin Templates - Ver3.3