With the help from this answer on stackoverflow the following now works.
--------------------------
-- Variant (A): Outdated
--------------------------
USE `acore_characters`;
-- Warlock pet creatures
SET @IMP := 416;
SET @VOIDWALKER := 1860;
SET @SUCCUBUS := 1863;
SET @FELHUNTER := 417;
SET @FELGUARD := 17252;
-- Names
SET @OWNER_NAME := 'MyWarlockName';
SET @IMP_NAME :='NewImpName';
SET @VOIDWALKER_NAME := 'NewVoidwalkerNamen';
SET @SUCCUBUS_NAME := 'NewSuccubusName';
SET @FELHUNTER_NAME := 'NewFelhunterName';
SET @FELGUARD_NAME := 'NewFelguardName';
-- Update Imp Name
UPDATE `character_pet` SET name = @IMP_NAME WHERE `id` IN
(SELECT s.`id` FROM (SELECT p.`id` FROM `character_pet` p INNER JOIN `characters` c ON p.`owner` = c.`guid`
WHERE c.`name` = @OWNER_NAME AND p.`entry`= @IMP) s);
-- Update Voidwalker Name
UPDATE `character_pet` SET name = @VOIDWALKER_NAME WHERE `id` IN
(SELECT s.`id` FROM (SELECT p.`id` FROM `character_pet` p INNER JOIN `characters` c ON p.`owner` = c.`guid`
WHERE c.`name` = @OWNER_NAME AND p.`entry`= @VOIDWALKER) s);
-- Update Succubus Name
UPDATE `character_pet` SET name = @SUCCUBUS_NAME WHERE `id` IN
(SELECT s.`id` FROM (SELECT p.`id` FROM `character_pet` p INNER JOIN `characters` c ON p.`owner` = c.`guid`
WHERE c.`name` = @OWNER_NAME AND p.`entry`= @SUCCUBUS) s);
-- Update Felhunter Name
UPDATE `character_pet` SET name = @FELHUNTER_NAME WHERE `id` IN
(SELECT s.`id` FROM (SELECT p.`id` FROM `character_pet` p INNER JOIN `characters` c ON p.`owner` = c.`guid`
WHERE c.`name` = @OWNER_NAME AND p.`entry`= @FELHUNTER) s);
-- Update Felguard Name
UPDATE `character_pet` SET name = @FELGUARD_NAME WHERE `id` IN
(SELECT s.`id` FROM (SELECT p.`id` FROM `character_pet` p INNER JOIN `characters` c ON p.`owner` = c.`guid`
WHERE c.`name` = @OWNER_NAME AND p.`entry`= @FELGUARD) s);
-- Show result
SELECT p.`id`, p.`entry`, p.`name`, c.`name` AS `owner_name`
FROM `character_pet` p INNER JOIN `characters` c ON p.`owner` = c.`guid`
WHERE c.`name` = @OWNER_NAME;
I neither like the additional nested SELECT
nor actually understand why MySQL complains without it, since the result of the inner SELECT
is not affected by the outer UPDATE
. The following variant uses the suggestion from the answer below and performs the updates on a JOIN
. Sadly I cannot use the "all in one update" method, since AzerothCore's minimum requirement for MySQL is 5.7 and the WITH ... VALUES
construct apparently requires 8.x.
--------------------------
-- Variant (B): Current
--------------------------
USE `acore_characters`;
-- Names (parameters)
SET @OWNERNAME := 'MyWarlockName';
SET @IMPNAME :='NewImpName';
SET @VOIDWALKERNAME := 'NewVoidwalkerNamen';
SET @SUCCUBUSNAME := 'NewSuccubusName';
SET @FELHUNTERNAME := 'NewFelhunterName';
SET @FELGUARDNAME := 'NewFelguardName';
-- Warlock pet creatures (constants)
SET @IMP := 416;
SET @VOIDWALKER := 1860;
SET @SUCCUBUS := 1863;
SET @FELHUNTER := 417;
SET @FELGUARD := 17252;
-- Update Imp Name
UPDATE `character_pet` p INNER JOIN `characters` c ON p.`owner` = c.`guid`
SET p.`name` = @IMPNAME
WHERE p.`entry` = @IMP AND c.`name` = @OWNERNAME;
-- Update Voidwalker Name
UPDATE `character_pet` p INNER JOIN `characters` c ON p.`owner` = c.`guid`
SET p.`name` = @VOIDWALKERNAME
WHERE p.`entry` = @VOIDWALKER AND c.`name` = @OWNERNAME;
-- Update Succubus Name
UPDATE `character_pet` p INNER JOIN `characters` c ON p.`owner` = c.`guid`
SET p.`name` = @SUCCUBUSNAME
WHERE p.`entry` = @SUCCUBUS AND c.`name` = @OWNERNAME;
-- Update Felhunter Name
UPDATE `character_pet` p INNER JOIN `characters` c ON p.`owner` = c.`guid`
SET p.`name` = @FELHUNTERNAME
WHERE p.`entry` = @FELHUNTER AND c.`name` = @OWNERNAME;
-- Update Felguard Name
UPDATE `character_pet` p INNER JOIN `characters` c ON p.`owner` = c.`guid`
SET p.`name` = @FELGUARDAME
WHERE p.`entry` = @FELGUARD AND c.`name` = @OWNERNAME;
-- Show result
SELECT p.`id`, p.`entry`, p.`name`, c.`name` AS `owner_name`
FROM `character_pet` p INNER JOIN `characters` c ON p.`owner` = c.`guid`
WHERE c.`name` = @OWNERNAME;