Spells usable by all classes

[Zaela_S]

The way the table is set up make this a bit of a pain...

DROP TEMPORARY TABLE IF EXISTS temp_spells_classes;
CREATE TEMPORARY TABLE temp_spells_classes (
	spell_id 	INT,
	level		INT
);

DROP TEMPORARY TABLE IF EXISTS temp_spells_classes_min;
CREATE TEMPORARY TABLE temp_spells_classes_min (
	spell_id 	INT PRIMARY KEY,
	level		INT
);

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes1 FROM spells_new WHERE classes1 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes2 FROM spells_new WHERE classes2 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes3 FROM spells_new WHERE classes3 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes4 FROM spells_new WHERE classes4 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes5 FROM spells_new WHERE classes5 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes6 FROM spells_new WHERE classes6 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes7 FROM spells_new WHERE classes7 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes8 FROM spells_new WHERE classes8 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes9 FROM spells_new WHERE classes9 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes10 FROM spells_new WHERE classes10 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes11 FROM spells_new WHERE classes11 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes12 FROM spells_new WHERE classes12 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes13 FROM spells_new WHERE classes13 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes14 FROM spells_new WHERE classes14 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes15 FROM spells_new WHERE classes15 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes16 FROM spells_new WHERE classes16 < 253;

INSERT INTO temp_spells_classes_min (spell_id, level)
SELECT spell_id, MIN(level)
FROM temp_spells_classes
GROUP BY spell_id;

UPDATE spells_new s
JOIN temp_spells_classes_min t ON s.id = t.spell_id
SET
	classes1 = t.level,
	classes2 = t.level,
	classes3 = t.level,
	classes4 = t.level,
	classes5 = t.level,
	classes6 = t.level,
	classes7 = t.level,
	classes8 = t.level,
	classes9 = t.level,
	classes10 = t.level,
	classes11 = t.level,
	classes12 = t.level,
	classes13 = t.level,
	classes14 = t.level,
	classes15 = t.level,
	classes16 = t.level;
	
DROP TEMPORARY TABLE IF EXISTS temp_spells_classes;
DROP TEMPORARY TABLE IF EXISTS temp_spells_classes_min;

Should put the above into a script (e.g. "example.sql") and run it with SOURCE (e.g. SOURCE "path/to/example.sql").

Back up spells_new first!!

[alberto667]

Thanks so much Zaela_S, that's exactly what I was looking for .. It works perfectly.

I was trying to figure it out by myself but I am still in the process of learning the more complex SQL functions, and as you said the way the table is set up is a pita...I now understand that the use of temp table was the key here...

Thanks again for your help

[alberto667]

After testing some more,I found that bard's songs could cause bugs ("you missed a note" instead of "your spell fizzle"), not sure for tanks tomes but better remove it too ..
Also when you put 65535 as item.class for spells scroll it shows ingame on it with all classes and a bunch of 255 for those that can't use it.

The code would then be for mage for instance ( Run this before Zaela_S's code) :

update items, spells_new set classes=classes+4096 WHERE
items.scrolleffect = spells_new.id AND
items.classes <> 4096 AND
spells_new.classes13 = 255 AND
items.`Name` LIKE '%spell:%'

Change the classe number from spells_new and the classe number for items from this chart to suit your needs :

Classes(number) in spells_new :
2 Cleric
3 Paladin
4 Ranger
5 Shadowknight
6 Druid
10 Shaman
11 Necromancer
12 Wizard
13 Magician
14 Enchanter
15 Beastlord

Classes in items :
1 Warrior
2 Cleric
4 Paladin
8 Ranger
16 Shadowknight
32 Druid
64 Monk
128 Bard
256 Rogue
512 Shaman
1024 Necromancer
2048 Wizard
4096 Magician
8192 Enchanter
16384 Beastlord


Then you can mod the classes in spells_new

DROP TEMPORARY TABLE IF EXISTS temp_spells_classes;
CREATE TEMPORARY TABLE temp_spells_classes (
	spell_id 	INT,
	level		INT
);

DROP TEMPORARY TABLE IF EXISTS temp_spells_classes_min;
CREATE TEMPORARY TABLE temp_spells_classes_min (
	spell_id 	INT PRIMARY KEY,
	level		INT
);

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes2 FROM spells_new WHERE classes2 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes3 FROM spells_new WHERE classes3 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes4 FROM spells_new WHERE classes4 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes5 FROM spells_new WHERE classes5 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes6 FROM spells_new WHERE classes6 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes10 FROM spells_new WHERE classes10 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes11 FROM spells_new WHERE classes11 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes12 FROM spells_new WHERE classes12 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes13 FROM spells_new WHERE classes13 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes14 FROM spells_new WHERE classes14 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes15 FROM spells_new WHERE classes15 < 253;

INSERT INTO temp_spells_classes_min (spell_id, level)
SELECT spell_id, MIN(level)
FROM temp_spells_classes
GROUP BY spell_id;

UPDATE spells_new s
JOIN temp_spells_classes_min t ON s.id = t.spell_id
SET
	classes2 = t.level,
	classes3 = t.level,
	classes4 = t.level,
	classes5 = t.level,
	classes6 = t.level,
	classes10 = t.level,
	classes11 = t.level,
	classes12 = t.level,
	classes13 = t.level,
	classes14 = t.level,
	classes15 = t.level;

	
DROP TEMPORARY TABLE IF EXISTS temp_spells_classes;
DROP TEMPORARY TABLE IF EXISTS temp_spells_classes_min;

Thanks again for the insight Zaela_S, and I hope it will help people like it helped me