Quiero escribir una consulta SQL para cambiar el nombre de mi(s) Warlock Minion(s) en AzerothCore , pero mi primer intento se encuentra con un error de MySQL:
MySQL Error 1093: no se puede especificar la tabla de destino para la actualización en la cláusula FROM
Mi código:
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);Con la ayuda de esta respuesta en stackoverflow, ahora funciona lo siguiente.
-------------------------- -- 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; No me gusta el SELECT anidado adicional ni entiendo por qué MySQL se queja sin él, ya que el resultado del SELECT interno no se ve afectado por el UPDATE externo. La siguiente variante usa la sugerencia de la respuesta a continuación y realiza las actualizaciones en un JOIN . Lamentablemente, no puedo usar el método de "actualización todo en uno", ya que el requisito mínimo de AzerothCore para MySQL es 5.7 y la construcción WITH ... VALUES aparentemente requiere 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;No necesita la subconsulta en absoluto, solo use un 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 Si está realizando varias actualizaciones, también puede unirse a una declaración de VALUES
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