SQL Query help

[louis1016]

I'm trying to import spells from one database to another, just as long as they share the same "id", "name", and the source databases "classes2" field is < 61

ive spent a long time trying to figure out how to sql query this and I cant figure it out, can anyone help?

[ProSkeptic]

Temp tables.

First, backup the original table (just in case) as :

CREATE IF NOT EXISTS bkp_table LIKE original_table;
INSERT INTO bkp_table select * FROM original_table;

Verify the counts, to be sure everything made it with this statement :
SELECT "original_count", count(*) FROM original_table UNION
SELECT "backup_count", count(*) FROM bkp_table;

Then try importing the data to a new table. We backed up the data JUST in case the import clobbers the old table.

Now, here's the secret...

Insert into a new 'scratch' table as :

INSERT INTO new_spell_table ( id, name, classes2 )
SELECT B.id, N.name, N.classes2
FROM first_spells_table A, imported_table B
WHERE A.classes2 < 61 AND A.id = B.id

Then check the new-spell table to see if it's got all you need.

If it's not good, tweak that query some

If it IS good then just move that new_spell_table into the real spell table.

Sounds like a lot of work, but I'm not entirely sure of which tables you need to use.

[louis1016]

Alright I wrote the SQL query, ran it, it executed, but it didnt change the spells for some reason, Heres my query -

REPLACE INTO
`spells_new`(
`player_1`,
`teleport_zone`,
`you_cast`,
`other_casts`,
`cast_on_you`,
`cast_on_other`,
`spell_fades`,
`range`,
`aoerange`,
`pushback`,
`pushup`,
`cast_time`,
`recovery_time`,
`recast_time`,
`buffdurationformula`,
`buffduration`,
`AEDuration`,
`mana`,
`effect_base_value1`,
`effect_base_value2`,
`effect_base_value3`,
`effect_base_value4`,
`effect_base_value5`,
`effect_base_value6`,
`effect_base_value7`,
`effect_base_value8`,
`effect_base_value9`,
`effect_base_value10`,
`effect_base_value11`,
`effect_base_value12`,
`effect_limit_value1`,
`effect_limit_value2`,
`effect_limit_value3`,
`effect_limit_value4`,
`effect_limit_value5`,
`effect_limit_value6`,
`effect_limit_value7`,
`effect_limit_value8`,
`effect_limit_value9`,
`effect_limit_value10`,
`effect_limit_value11`,
`effect_limit_value12`,
`max1`,
`max2`,
`max3`,
`max4`,
`max5`,
`max6`,
`max7`,
`max8`,
`max9`,
`max10`,
`max11`,
`max12`,
`components1`,
`components2`,
`components3`,
`components4`,
`component_counts1`,
`component_counts2`,
`component_counts3`,
`component_counts4`,
`NoexpendReagent1`,
`NoexpendReagent2`,
`NoexpendReagent3`,
`NoexpendReagent4`,
`formula1`,
`formula2`,
`formula3`,
`formula4`,
`formula5`,
`formula6`,
`formula7`,
`formula8`,
`formula9`,
`formula10`,
`formula11`,
`formula12`,
`LightType`,
`goodEffect`,
`Activated`,
`resisttype`,
`effectid1`,
`effectid2`,
`effectid3`,
`effectid4`,
`effectid5`,
`effectid6`,
`effectid7`,
`effectid8`,
`effectid9`,
`effectid10`,
`effectid11`,
`effectid12`,
`targettype`,
`basediff`,
`skill`,
`zonetype`,
`EnvironmentType`,
`TimeOfDay`,
`classes1`,
`classes2`,
`classes3`,
`classes4`,
`classes5`,
`classes6`,
`classes7`,
`classes8`,
`classes9`,
`classes10`,
`classes11`,
`classes12`,
`classes13`,
`classes14`,
`classes15`,
`classes16`,
`TravelType`,
`SpellAffectIndex`,
`field124`,
`field125`,
`deities1`,
`deities2`,
`deities3`,
`deities4`,
`deities5`,
`deities6`,
`deities7`,
`deities8`,
`deities9`,
`deities10`,
`deities11`,
`deities12`,
`deities13`,
`deities14`,
`deities15`,
`deities16`,
`field142`,
`field143`,
`uninterruptable`,
`ResistDiff`,
`dot_stacking_exempt`,
`deleteable`,
`RecourseLink`,
`field151`,
`field152`,
`field153`,
`short_buff_box`,
`descnum`,
`typedescnum`,
`effectdescnum`,
`field158`,
`field159`,
`field160`,
`field161`,
`bonushate`,
`field163`,
`field164`,
`field165`,
`EndurCost`,
`EndurTimerIndex`,
`field168`,
`field169`,
`field170`,
`field171`,
`field172`,
`HateAdded`,
`EndurUpkeep`,
`field175`,
`numhits`,
`pvpresistbase`,
`pvpresistcalc`,
`pvpresistcap`,
`spell_category`,
`field181`,
`field182`,
`field183`,
`field184`,
`can_mgb`,
`nodispell`,
`npc_category`,
`npc_usefulness`,
`field189`,
`field190`,
`field191`,
`field192`,
`nimbuseffect`,
`field194`,
`field195`,
`field196`,
`field197`,
`field198`,
`field199`,
`field200`,
`field201`,
`field202`,
`field203`,
`field204`,
`field205`,
`field206`,
`spellgroup`,
`field208`,
`field209`,
`field210`,
`field211`,
`allowrest`,
`field213`,
`field214`)
SELECT
`spells_old`.`player_1`,
`spells_old`.`teleport_zone`,
`spells_old`.`you_cast`,
`spells_old`.`other_casts`,
`spells_old`.`cast_on_you`,
`spells_old`.`cast_on_other`,
`spells_old`.`spell_fades`,
`spells_old`.`range`,
`spells_old`.`aoerange`,
`spells_old`.`pushback`,
`spells_old`.`pushup`,
`spells_old`.`cast_time`,
`spells_old`.`recovery_time`,
`spells_old`.`recast_time`,
`spells_old`.`buffdurationformula`,
`spells_old`.`buffduration`,
`spells_old`.`AEDuration`,
`spells_old`.`mana`,
`spells_old`.`effect_base_value1`,
`spells_old`.`effect_base_value2`,
`spells_old`.`effect_base_value3`,
`spells_old`.`effect_base_value4`,
`spells_old`.`effect_base_value5`,
`spells_old`.`effect_base_value6`,
`spells_old`.`effect_base_value7`,
`spells_old`.`effect_base_value8`,
`spells_old`.`effect_base_value9`,
`spells_old`.`effect_base_value10`,
`spells_old`.`effect_base_value11`,
`spells_old`.`effect_base_value12`,
`spells_old`.`effect_limit_value1`,
`spells_old`.`effect_limit_value2`,
`spells_old`.`effect_limit_value3`,
`spells_old`.`effect_limit_value4`,
`spells_old`.`effect_limit_value5`,
`spells_old`.`effect_limit_value6`,
`spells_old`.`effect_limit_value7`,
`spells_old`.`effect_limit_value8`,
`spells_old`.`effect_limit_value9`,
`spells_old`.`effect_limit_value10`,
`spells_old`.`effect_limit_value11`,
`spells_old`.`effect_limit_value12`,
`spells_old`.`max1`,
`spells_old`.`max2`,
`spells_old`.`max3`,
`spells_old`.`max4`,
`spells_old`.`max5`,
`spells_old`.`max6`,
`spells_old`.`max7`,
`spells_old`.`max8`,
`spells_old`.`max9`,
`spells_old`.`max10`,
`spells_old`.`max11`,
`spells_old`.`max12`,
`spells_old`.`components1`,
`spells_old`.`components2`,
`spells_old`.`components3`,
`spells_old`.`components4`,
`spells_old`.`component_counts1`,
`spells_old`.`component_counts2`,
`spells_old`.`component_counts3`,
`spells_old`.`component_counts4`,
`spells_old`.`NoexpendReagent1`,
`spells_old`.`NoexpendReagent2`,
`spells_old`.`NoexpendReagent3`,
`spells_old`.`NoexpendReagent4`,
`spells_old`.`formula1`,
`spells_old`.`formula2`,
`spells_old`.`formula3`,
`spells_old`.`formula4`,
`spells_old`.`formula5`,
`spells_old`.`formula6`,
`spells_old`.`formula7`,
`spells_old`.`formula8`,
`spells_old`.`formula9`,
`spells_old`.`formula10`,
`spells_old`.`formula11`,
`spells_old`.`formula12`,
`spells_old`.`LightType`,
`spells_old`.`goodEffect`,
`spells_old`.`Activated`,
`spells_old`.`resisttype`,
`spells_old`.`effectid1`,
`spells_old`.`effectid2`,
`spells_old`.`effectid3`,
`spells_old`.`effectid4`,
`spells_old`.`effectid5`,
`spells_old`.`effectid6`,
`spells_old`.`effectid7`,
`spells_old`.`effectid8`,
`spells_old`.`effectid9`,
`spells_old`.`effectid10`,
`spells_old`.`effectid11`,
`spells_old`.`effectid12`,
`spells_old`.`targettype`,
`spells_old`.`basediff`,
`spells_old`.`skill`,
`spells_old`.`zonetype`,
`spells_old`.`EnvironmentType`,
`spells_old`.`TimeOfDay`,
`spells_old`.`classes1`,
`spells_old`.`classes2`,
`spells_old`.`classes3`,
`spells_old`.`classes4`,
`spells_old`.`classes5`,
`spells_old`.`classes6`,
`spells_old`.`classes7`,
`spells_old`.`classes8`,
`spells_old`.`classes9`,
`spells_old`.`classes10`,
`spells_old`.`classes11`,
`spells_old`.`classes12`,
`spells_old`.`classes13`,
`spells_old`.`classes14`,
`spells_old`.`classes15`,
`spells_old`.`classes16`,
`spells_old`.`TravelType`,
`spells_old`.`SpellAffectIndex`,
`spells_old`.`field124`,
`spells_old`.`field125`,
`spells_old`.`deities1`,
`spells_old`.`deities2`,
`spells_old`.`deities3`,
`spells_old`.`deities4`,
`spells_old`.`deities5`,
`spells_old`.`deities6`,
`spells_old`.`deities7`,
`spells_old`.`deities8`,
`spells_old`.`deities9`,
`spells_old`.`deities10`,
`spells_old`.`deities11`,
`spells_old`.`deities12`,
`spells_old`.`deities13`,
`spells_old`.`deities14`,
`spells_old`.`deities15`,
`spells_old`.`deities16`,
`spells_old`.`field142`,
`spells_old`.`field143`,
`spells_old`.`uninterruptable`,
`spells_old`.`ResistDiff`,
`spells_old`.`dot_stacking_exempt`,
`spells_old`.`deleteable`,
`spells_old`.`RecourseLink`,
`spells_old`.`field151`,
`spells_old`.`field152`,
`spells_old`.`field153`,
`spells_old`.`short_buff_box`,
`spells_old`.`descnum`,
`spells_old`.`typedescnum`,
`spells_old`.`effectdescnum`,
`spells_old`.`field158`,
`spells_old`.`field159`,
`spells_old`.`field160`,
`spells_old`.`field161`,
`spells_old`.`bonushate`,
`spells_old`.`field163`,
`spells_old`.`field164`,
`spells_old`.`field165`,
`spells_old`.`EndurCost`,
`spells_old`.`EndurTimerIndex`,
`spells_old`.`field168`,
`spells_old`.`field169`,
`spells_old`.`field170`,
`spells_old`.`field171`,
`spells_old`.`field172`,
`spells_old`.`HateAdded`,
`spells_old`.`EndurUpkeep`,
`spells_old`.`field175`,
`spells_old`.`numhits`,
`spells_old`.`pvpresistbase`,
`spells_old`.`pvpresistcalc`,
`spells_old`.`pvpresistcap`,
`spells_old`.`spell_category`,
`spells_old`.`field181`,
`spells_old`.`field182`,
`spells_old`.`field183`,
`spells_old`.`field184`,
`spells_old`.`can_mgb`,
`spells_old`.`nodispell`,
`spells_old`.`npc_category`,
`spells_old`.`npc_usefulness`,
`spells_old`.`field189`,
`spells_old`.`field190`,
`spells_old`.`field191`,
`spells_old`.`field192`,
`spells_old`.`nimbuseffect`,
`spells_old`.`field194`,
`spells_old`.`field195`,
`spells_old`.`field196`,
`spells_old`.`field197`,
`spells_old`.`field198`,
`spells_old`.`field199`,
`spells_old`.`field200`,
`spells_old`.`field201`,
`spells_old`.`field202`,
`spells_old`.`field203`,
`spells_old`.`field204`,
`spells_old`.`field205`,
`spells_old`.`field206`,
`spells_old`.`spellgroup`,
`spells_old`.`field208`,
`spells_old`.`field209`,
`spells_old`.`field210`,
`spells_old`.`field211`,
`spells_old`.`allowrest`,
`spells_old`.`field213`,
`spells_old`.`field214`
FROM
`spells_old`,
`spells_new`
WHERE
`spells_old`.`name` = `spells_new`.`name` AND
`spells_old`.`classes2` <= 60;

I decided against the ID check, just the name check, but here it is. I dont know whats wrong, no errors, but its not doing its job. Any help?

[louis1016]

basically im trying to update the peq spellfile with the p99 classic spells minus the p99 icons, animations, and particles, any idea how i can do this?

[ProSkeptic]

The problem was that MySQL is using that primary key as the replace logic marker... without that it's doing an insert with an primary keys, so it's giving it zero for the ID -- over and over and over again. So, all your records were going in, as ID 0, each rewriting the previous one (kinda cruel).

Here is the fix, and it's simple.

Add the ID to the REPLACE clause and the SELECT clause-- as :

REPLACE INTO
`spells_new`(
`id`,
`player_1`,
`teleport_zone`,
`you_cast`,
`other_casts`,
...
SELECT
`spells_old`.`id`,
`spells_old`.`player_1`,
`spells_old`.`teleport_zone`,
`spells_old`.`you_cast`,
`spells_old`.`other_casts`,
...
FROM
`spells_old`,
`spells_new`
WHERE
`spells_old`.`name` = `spells_new`.`name` AND
`spells_old`.`classes2` <= 60;

but DO NOT add the ID column as a criterion to the WHERE clause at the bottom. You could add it, but it's not needed.

[louis1016]

I got it to work, thanks a lot