Almost there! Looking for some coding experts...
I have the following code based on Zaela's above... this reads from spells_new table and creates the correct classes bitmask is the items table for all spell scrolls.
I confirmed the temp table operates properly, the problem is in updating the items table via JOIN. This code I have below causes HeidiSQL to hang and then ultimately crash SQL. Am I missing something that is causing this?
Code:
UPDATE items s
JOIN temp_spells_classes_sum t ON s.scrolleffect = t.spell_id
SET classes = t.classes_id
WHERE s.scrolltype = '7'
;
The entire script
Code:
DROP TEMPORARY TABLE IF EXISTS temp_spells_classes;
CREATE TEMPORARY TABLE temp_spells_classes (
spell_id INT,
level1 INT DEFAULT '0',
level2 INT DEFAULT '0',
level3 INT DEFAULT '0',
level4 INT DEFAULT '0',
level5 INT DEFAULT '0',
level6 INT DEFAULT '0',
level7 INT DEFAULT '0',
level8 INT DEFAULT '0',
level9 INT DEFAULT '0',
level10 INT DEFAULT '0',
level11 INT DEFAULT '0',
level12 INT DEFAULT '0',
level13 INT DEFAULT '0',
level14 INT DEFAULT '0',
level15 INT DEFAULT '0',
level16 INT DEFAULT '0'
);
DROP TEMPORARY TABLE IF EXISTS temp_spells_classes_sum;
CREATE TEMPORARY TABLE temp_spells_classes_sum (
spell_id MEDIUMINT NOT NULL,
classes_id MEDIUMINT NOT NULL
);
INSERT INTO temp_spells_classes (spell_id, level1)
SELECT id, classes1/classes1*1 FROM spells_new WHERE classes1 < 253;
INSERT INTO temp_spells_classes (spell_id, level2)
SELECT id, classes2/classes2*2 FROM spells_new WHERE classes2 < 253;
INSERT INTO temp_spells_classes (spell_id, level3)
SELECT id, classes3/classes3*4 FROM spells_new WHERE classes3 < 253;
INSERT INTO temp_spells_classes (spell_id, level4)
SELECT id, classes4/classes4*8 FROM spells_new WHERE classes4 < 253;
INSERT INTO temp_spells_classes (spell_id, level5)
SELECT id, classes5/classes5*16 FROM spells_new WHERE classes5 < 253;
INSERT INTO temp_spells_classes (spell_id, level6)
SELECT id, classes6/classes6*32 FROM spells_new WHERE classes6 < 253;
INSERT INTO temp_spells_classes (spell_id, level7)
SELECT id, classes7/classes7*64 FROM spells_new WHERE classes7 < 253;
INSERT INTO temp_spells_classes (spell_id, level8)
SELECT id, classes8/classes8*128 FROM spells_new WHERE classes8 < 253;
INSERT INTO temp_spells_classes (spell_id, level9)
SELECT id, classes9/classes9*256 FROM spells_new WHERE classes9 < 253;
INSERT INTO temp_spells_classes (spell_id, level10)
SELECT id, classes10/classes10*512 FROM spells_new WHERE classes10 < 253;
INSERT INTO temp_spells_classes (spell_id, level11)
SELECT id, classes11/classes11*1024 FROM spells_new WHERE classes11 < 253;
INSERT INTO temp_spells_classes (spell_id, level12)
SELECT id, classes12/classes12*2048 FROM spells_new WHERE classes12 < 253;
INSERT INTO temp_spells_classes (spell_id, level13)
SELECT id, classes13/classes13*4096 FROM spells_new WHERE classes13 < 253;
INSERT INTO temp_spells_classes (spell_id, level14)
SELECT id, classes14/classes14*8192 FROM spells_new WHERE classes14 < 253;
INSERT INTO temp_spells_classes (spell_id, level15)
SELECT id, classes15/classes15*16384 FROM spells_new WHERE classes15 < 253;
INSERT INTO temp_spells_classes (spell_id, level16)
SELECT id, classes16/classes16*32768 FROM spells_new WHERE classes16 < 253;
INSERT INTO temp_spells_classes_sum (spell_id, classes_id)
SELECT spell_id, SUM(level1+level2+level3+level4+level5+level6+level7+level8+level9+level10+level11+level12+level13+level14+level15+level16)
FROM temp_spells_classes
GROUP BY spell_id;
UPDATE items s
JOIN temp_spells_classes_sum t ON s.scrolleffect = t.spell_id
SET classes = t.classes_id
WHERE s.scrolltype = '7'
;
DROP TEMPORARY TABLE IF EXISTS temp_spells_classes;
DROP TEMPORARY TABLE IF EXISTS temp_spells_classes_sum;