How do I rename Warlock Minions on AzerothCore with an SQL statement?

Question

I want to write an SQL query to rename my Warlock Minion(s) on AzerothCore, but my first attempt runs into a MySQL error:

MySQL Error 1093 - Can't specify target table for update in FROM clause

My code:

USE `acore_characters`;
SET @IMP := 416;
SET @IMP_NAME = 'NewImpName';
SET @OWNER_NAME = 'NameOfMyWarlock';
UPDATE `character_pet` SET name = @IMP_NAME WHERE `id` IN
  (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);

Answer 1

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;

Answer 2

You don't need the subquery at all, just use a JOIN

UPDATE character_pet p
INNER JOIN characters c 
    ON p.owner = c.guid 
    AND c.name = @OWNER_NAME 
    AND p.entry = @IMP
SET name = @IMP_NAME

If you are doing multiple updates, you can also join to a VALUES statement

WITH vals(id, newname) AS (
    VALUES (416, 'NewImpName'),
           (1860, 'NewVoidwalkerName')
           (1863, 'NewSuccubusName')
           (417, 'NewFelhunterName')
           (17252, 'NewFelguardName'))
UPDATE character_pet p
INNER JOIN vals v
    ON p.entry = v.id
INNER JOIN characters c 
    ON p.owner = c.guid
    AND c.name = 'MyWarlockName'
SET p.name = v.newname