mysql - 如何使用 SQL 语句重命名 AzerothCore 上的 Warlock Minions？

Question

我想写一个 SQL 查询来重命名我在 AzerothCore 上的 Warlock Minion(s) ，但我的第一次尝试遇到了 MySQL 错误:

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

我的代码:

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

在 this answer on stackoverflow 的帮助下以下现在有效。

--------------------------
-- 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;

我不喜欢额外的嵌套 SELECT也不明白为什么没有它 MySQL 会提示，因为内部 SELECT 的结果不受外 UPDATE影响.以下变体使用来自 the answer below 的建议并在 JOIN 上执行更新.遗憾的是我不能使用“多合一更新”方法，因为 AzerothCore 对 MySQL 的最低要求是 5.7，而 WITH ... VALUES构造显然需要 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;