SQL Tips - Mass Edit

[GRUMPY]

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

[Kingly_Krab]

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.

[GRUMPY]

Originally Posted by Kingly_Krab

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.