Required items

[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)
	)

[cavedude]

Originally Posted by 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)
	)

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

[Shadow-Wolf]

Originally Posted by 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)
	)

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""