Required items

[Shadow-Wolf]

Hello folks, need a little help. It's been forever since I've done anything eqemu related and I would like some info if anyone has it. What I am wanting to do is simple, I want to wipe my item table clean except for all items required for functioning spells and other misc required items. If anyone has a list of these items I would greatly appreciate you sharing it. If not I will look my self, I am simply trying to not reinvent the wheel here. Thank you for your time.

[trevius]

What I am wanting to do is simple, I want to wipe my item table clean except for all items required for functioning spells and other misc required items.

Not exactly sure what you mean by this. No items are required for anything. Though, if you only want certain items available on your server, you might be better off wiping out the loot tables and creating new ones with the select items you want to have available. There isn't much need to clear out the items table as there are thousands of IDs still available that you can create items in.

[Shadow-Wolf]

What I mean are items like bone chips and peridots, ttems that spells consume when they are used. I want to wipe the item database and build a new one with custom items organized in a way that makes development of a custom world easier for me. I just don't want to bother with the spell files therefore I want to leave the items consumed by them.

[drakelord]

Quickest way I can think of is to make a list of all the item id's required by the spells, then just run a query in your choice of MySQL browser selecting for any entries with those ids. Back those up, wipe the table, then restore, and you have yourself an empty table.

[Secrets]

Simpler way is to use excel or a similar program to make a query for you. You can sort by reagent ID in navicat, and copy-paste it into excel. Something like "Select * from items where id = (id from navicat) and
id = | (next id from navicat) | and" with the | being a delimiter for the cell. Then, you can copy-paste into navicat, run the query, and use navicat's export tools to export all of the item IDs that are used by the spells table, and then empty the table (also using navicat) and import the SQL you also exported. Try that, see if it works.

If all else fails look into JOIN queries.

Protip: Navicat's ordering views + Excel = heaven.

[GeorgeS]

Please use a simplied sql query rather than a long string

SELECT name from items i where id IN (1003,1001,1002)

returns name where item id = 1001-1003

GeorgeS

[AndMetal]

I'm sure the syntax is 100% correct, but you should be able to do something like this:


DELETE FROM items
WHERE id NOT IN (
(SELECT components1 FROM spells_new WHERE components1 > 1000),
(SELECT components2 FROM spells_new WHERE components2 > 1000),
(SELECT components3 FROM spells_new WHERE components3 > 1000),
(SELECT components4 FROM spells_new WHERE components4 > 1000),
(SELECT NoexpendReagent1 FROM spells_new WHERE NoexpendReagent1 > 1000),
(SELECT NoexpendReagent2 FROM spells_new WHERE NoexpendReagent2 > 1000),
(SELECT NoexpendReagent3 FROM spells_new WHERE NoexpendReagent3 > 1000),
(SELECT NoexpendReagent4 FROM spells_new WHERE NoexpendReagent4 > 1000)
)

[pfyon]

I was working on a query last night similar to AndMetal's, but it ended up running for about 2 hours before I killed it.

[cavedude]

I'm sure the syntax is 100% correct, but you should be able to do something like this:


DELETE FROM items
WHERE id NOT IN (
(SELECT components1 FROM spells_new WHERE components1 > 1000),
(SELECT components2 FROM spells_new WHERE components2 > 1000),
(SELECT components3 FROM spells_new WHERE components3 > 1000),
(SELECT components4 FROM spells_new WHERE components4 > 1000),
(SELECT NoexpendReagent1 FROM spells_new WHERE NoexpendReagent1 > 1000),
(SELECT NoexpendReagent2 FROM spells_new WHERE NoexpendReagent2 > 1000),
(SELECT NoexpendReagent3 FROM spells_new WHERE NoexpendReagent3 > 1000),
(SELECT NoexpendReagent4 FROM spells_new WHERE NoexpendReagent4 > 1000)
)


effect_base_value1, 2, 3, and 4 are also used, to summon items I believe.

[Shadow-Wolf]

I'm sure the syntax is 100% correct, but you should be able to do something like this:


DELETE FROM items
WHERE id NOT IN (
(SELECT components1 FROM spells_new WHERE components1 > 1000),
(SELECT components2 FROM spells_new WHERE components2 > 1000),
(SELECT components3 FROM spells_new WHERE components3 > 1000),
(SELECT components4 FROM spells_new WHERE components4 > 1000),
(SELECT NoexpendReagent1 FROM spells_new WHERE NoexpendReagent1 > 1000),
(SELECT NoexpendReagent2 FROM spells_new WHERE NoexpendReagent2 > 1000),
(SELECT NoexpendReagent3 FROM spells_new WHERE NoexpendReagent3 > 1000),
(SELECT NoexpendReagent4 FROM spells_new WHERE NoexpendReagent4 > 1000)
)


With a little modification adding the fields Cavedude specified this will give me exactly what I needed. Thank you everyone for your help!

[Shadow-Wolf]

Hmm, while the syntax throws no errors there is something wrong with the statements logic as it deletes no items at all, looking into it.

Edit - When running this through an sql validator I get this

The following feature outside Core SQL-200x (draft) is used:
F561, "Full value expressions""