SQL Fix

For SQL fixes:

• search for existing issues. If a bug report exists for the issue you are trying to fix, and it's open, post your suggestion there

We prefer to post suggestions in already open issues since they usually contain useful info such as comments from contributors, fixes, links to various sources

• always start suggestion by -- in case previous SQL file has no newline

--
UPDATE `creature` SET `wander_distance` = 5, `MovementType` = 1 WHERE `guid` = 91235 AND `id` = 19307;

• don't modify WDB fields without providing sniffs of those fields (censure guids, character names, anything blizzard can use to identify source)
• don't hardcode guids (use guid from fixed guid range when adding new objects or creatures)

In 3.3.5 creature guid range is between 1 and 250000, gameobject guid range is between 1 and 200000

-- Wrong:
DELETE FROM `creature` WHERE `guid` = 50000000 AND `id` = 19307;
INSERT INTO `creature` (`guid`, `id`, `map`, `zoneId`, `areaId`, `spawnMask`, `phaseMask`, `modelid`, `equipment_id`, `position_x`, `position_y`, `position_z`, `orientation`, `spawntimesecs`, `wander_distance`, `currentwaypoint`, `curhealth`, `curmana`, `MovementType`, `npcflag`, `unit_flags`, `dynamicflags`, `ScriptName`, `VerifiedBuild`) VALUES
(50000000,19307,557,0,0,3,1,0,0,-372.748,-147.504,-0.979576,1.17386,7200,5,0,30004,0,1,0,0,0,'',0);

-- Correct:
DELETE FROM `creature` WHERE `guid` = 91235 AND `id` = 19307;
INSERT INTO `creature` (`guid`, `id`, `map`, `zoneId`, `areaId`, `spawnMask`, `phaseMask`, `modelid`, `equipment_id`, `position_x`, `position_y`, `position_z`, `orientation`, `spawntimesecs`, `wander_distance`, `currentwaypoint`, `curhealth`, `curmana`, `MovementType`, `npcflag`, `unit_flags`, `dynamicflags`, `ScriptName`, `VerifiedBuild`) VALUES
(91235,19307,557,0,0,3,1,0,0,-372.748,-147.504,-0.979576,1.17386,7200,5,0,30004,0,1,0,0,0,'',0);

• don't DELETE + INSERT to update only few fields of one row

Re-inserting a whole row to change one or more fields makes it harder to review and track changes. Reasonable only if all data is going to be changed(only few tables such as smart_scripts)

-- Wrong:
DELETE FROM `gameobject_loot_template` WHERE `Entry` = 28682 AND `Item` = 35723 AND `Reference` = 0;
INSERT INTO `gameobject_loot_template` (`Entry`, `Item`, `Reference`, `Chance`, `QuestRequired`, `LootMode`, `GroupId`, `MinCount`, `MaxCount`, `Comment`) VALUES
(28682,35723,0,100,0,1,0,1,1,NULL);

-- Correct:
UPDATE `gameobject_loot_template` SET `Chance` = 100 WHERE `Entry` = 28682 AND `Item` = 35723 AND `Reference` = 0;

• don't use database names

Some users can name their world database differently. If database name is set directly in SQL file, that file will throw error

-- Wrong:
UPDATE `world`.`creature`

-- Correct:
UPDATE `creature`

• don't use ' around numbers

-- Wrong:
DELETE FROM `creature` WHERE `guid` = '91235' AND `id` = '19307';
INSERT INTO `creature` (`guid`, `id`, `map`, `zoneId`, `areaId`, `spawnMask`, `phaseMask`, `modelid`, `equipment_id`, `position_x`, `position_y`, `position_z`, `orientation`, `spawntimesecs`, `wander_distance`, `currentwaypoint`, `curhealth`, `curmana`, `MovementType`, `npcflag`, `unit_flags`, `dynamicflags`, `ScriptName`, `VerifiedBuild`) VALUES
('91235','19307','557','0','0','3','1','0','0','-372.748','-147.504','-0.979576','1.17386','7200','5','0','30004','0','1','0','0','0','','0');

-- Correct:
DELETE FROM `creature` WHERE `guid` = 91235 AND `id` = 19307;
INSERT INTO `creature` (`guid`, `id`, `map`, `zoneId`, `areaId`, `spawnMask`, `phaseMask`, `modelid`, `equipment_id`, `position_x`, `position_y`, `position_z`, `orientation`, `spawntimesecs`, `wander_distance`, `currentwaypoint`, `curhealth`, `curmana`, `MovementType`, `npcflag`, `unit_flags`, `dynamicflags`, `ScriptName`, `VerifiedBuild`) VALUES
(91235,19307,557,0,0,3,1,0,0,-372.748,-147.504,-0.979576,1.17386,7200,5,0,30004,0,1,0,0,0,'',0);

• don't use ( ) if they aren't needed

-- Wrong:
DELETE FROM `smart_scripts` WHERE (`entryorguid` = 23094 AND `source_type` = 0);

-- Correct:
DELETE FROM `smart_scripts` WHERE `entryorguid` = 23094 AND `source_type` = 0;

• use only one INSERT INTO statement when you add multiple rows to one table, unless you are scripting multiple creatures

-- Wrong:
DELETE FROM `creature_questender` WHERE `id` = 28012 AND `quest` IN (13126, 13127);
INSERT INTO `creature_questender` (`id`, `quest`) VALUES (28012,13126);
INSERT INTO `creature_questender` (`id`, `quest`) VALUES (28012,13127);

-- Correct:
DELETE FROM `creature_questender` WHERE `id` = 28012 AND `quest` IN (13126, 13127);
INSERT INTO `creature_questender` (`id`, `quest`) VALUES
(28012,13126),
(28012,13127);

• always include column names in every INSERT INTO statement

-- Wrong:
INSERT INTO `gameobject_loot_template` VALUES (28682,35723,0,100,0,1,0,1,1,NULL);

-- Correct:
INSERT INTO `gameobject_loot_template` (`Entry`, `Item`, `Reference`, `Chance`, `QuestRequired`, `LootMode`, `GroupId`, `MinCount`, `MaxCount`, `Comment`) VALUES
(28682,35723,0,100,0,1,0,1,1,NULL);

• always make every INSERT INTO statement runnable twice (delete what you insert even if row does not exist, don't use REPLACE INTO)

-- Wrong:
INSERT INTO `smart_scripts` (`entryorguid`,`source_type`,`id`,`link`,`event_type`,`event_phase_mask`,`event_chance`,`event_flags`,`event_param1`,`event_param2`,`event_param3`,`event_param4`,`event_param5`,`action_type`,`action_param1`,`action_param2`,`action_param3`,`action_param4`,`action_param5`,`action_param6`,`target_type`,`target_param1`,`target_param2`,`target_param3`,`target_param4`,`target_x`,`target_y`,`target_z`,`target_o`,`comment`) VALUES
(181148,1,0,0,70,0,100,0,2,0,0,0,0,41,3000,0,0,0,0,0,1,0,0,0,0,0,0,0,0,"Mummified Troll Remains - On Gameobject State Changed - Delayed Despawn");

-- Correct:
DELETE FROM `smart_scripts` WHERE `entryorguid` = 181148 AND `source_type` = 1;
INSERT INTO `smart_scripts` (`entryorguid`,`source_type`,`id`,`link`,`event_type`,`event_phase_mask`,`event_chance`,`event_flags`,`event_param1`,`event_param2`,`event_param3`,`event_param4`,`event_param5`,`action_type`,`action_param1`,`action_param2`,`action_param3`,`action_param4`,`action_param5`,`action_param6`,`target_type`,`target_param1`,`target_param2`,`target_param3`,`target_param4`,`target_x`,`target_y`,`target_z`,`target_o`,`comment`) VALUES
(181148,1,0,0,70,0,100,0,2,0,0,0,0,41,3000,0,0,0,0,0,1,0,0,0,0,0,0,0,0,"Mummified Troll Remains - On Gameobject State Changed - Delayed Despawn");

• DELETE by guid AND id to be sure we don't delete existing spawns

-- Wrong:
DELETE FROM `creature` WHERE `guid` = 91235;
DELETE FROM `gameobject` WHERE `guid` = 54235;

-- Correct:
DELETE FROM `creature` WHERE `guid` = 91235 AND `id` = 19307;
DELETE FROM `gameobject` WHERE `guid` = 54235 AND `id` = 181401;

Find an example of a proper SQL Fix filled issue at <to be filled>