mysql - 更新具有相同 id 的多行

Question

我正在开发 MaNGOS 数据库。目前我已经有了自己的ID，但我想使用官方的一次。问题是我的数据库中已经有一些我想要更新的值。

我想要更新的表格如下所示。

mysql> SELECT * FROM `character` LIMIT 10;
+----+---------------------------------+--------------+--------------+-------+
| id | char_name_de                    | char_name_en | id_new       | id_old|
+----+---------------------------------+--------------+--------------+-------+
|  1 |                                 | NULL         |            2 |  NULL |
|  2 | Abgesandter des Schattenhammers | NULL         |        18201 |  NULL |
|  3 | Abgesplittertes Skelett         | NULL         |        10478 |  NULL |
|  4 | Adept aus Scholomance           | NULL         |        10469 |  NULL |
|  5 | Aduscha                         | NULL         |        18204 |  NULL |
|  6 | Aggressiver Bluthund            | NULL         |         8922 |  NULL |
|  7 | Akolyt aus Scholomance          | NULL         |        10471 |  NULL |
|  8 | Akolyt der Schmetterschilde     | NULL         |         9045 |  NULL |
|  9 | Althena                         | NULL         |        18208 |  NULL |
| 10 | Amadelis                        | NULL         |        18209 |  NULL |
+----+---------------------------------+--------------+--------------+-------+
10 rows in set (0.00 sec)

我正在尝试做的事情:将“id”替换为“new_id”

问题:“id_new”并不是某些多语言字符串的唯一原因。 我想将它们匹配成一行，将“id”保存到“id_old”中，将“id”替换为“id_new”并删除“id_new”。

看起来像这样:

mysql> SELECT *
    -> FROM `character`
    -> WHERE id_new IN(
    -> SELECT id_new
    -> FROM `character`
    -> GROUP BY `character`.id_new
    -> HAVING COUNT(`character`.id_new) > 1
    -> )
    -> ORDER BY id_new ASC;
+-----+--------------------------+--------------+--------------+-------+
| id  | char_name_de             | char_name_en | id_new       | id_old|
+-----+--------------------------+--------------+--------------+-------+
| 258 | Natter                   | NULL         |         2914 |  NULL |
| 342 | Snake                    | NULL         |         2914 |  NULL |
| 290 | Rat                      | NULL         |         4075 |  NULL |
| 292 | Ratte                    | NULL         |         4075 |  NULL |
| 276 | Orakel der Hakkari       | NULL         |        11346 |  NULL |
| 152 | Hakkari Oracle           | NULL         |        11346 |  NULL |
| 148 | Gurubashi Warrior        | NULL         |        11355 |  NULL |
| 201 | Krieger der Gurubashi    | NULL         |        11355 |  NULL |
| 344 | Sohn von Hakkar          | NULL         |        11357 |  NULL |
| 347 | Son of Hakkar            | NULL         |        11357 |  NULL |
|  47 | Bloodseeker Bat          | NULL         |        11368 |  NULL |
|  51 | Blutsucherfledermaus     | NULL         |        11368 |  NULL |
| 560 | Molten Giant             | NULL         |        11658 |  NULL |
| 123 | Geschmolzener Riese      | NULL         |        11658 |  NULL |
| 545 | Flamewaker               | NULL         |        11661 |  NULL |
|  93 | Feuerschuppe             | NULL         |        11661 |  NULL |
| 546 | Flamewaker Priest        | NULL         |        11662 |  NULL |
|  94 | Feuerschuppenpriester    | NULL         |        11662 |  NULL |
| 553 | Lava Annihilator         | NULL         |        11665 |  NULL |
| 214 | Lavavernichter           | NULL         |        11665 |  NULL |
| 540 | Firelord                 | NULL         |        11668 |  NULL |
|  92 | Feuerlord                | NULL         |        11668 |  NULL |
| 543 | Flame Imp                | NULL         |        11669 |  NULL |
| 104 | Flammenwichtel           | NULL         |        11669 |  NULL |
| 536 | Core Hound               | NULL         |        11671 |  NULL |
| 194 | Kernhund                 | NULL         |        11671 |  NULL |
| 534 | Ancient Core Hound       | NULL         |        11673 |  NULL |
| 384 | Uralter Kernhund         | NULL         |        11673 |  NULL |
| 549 | Golemagg the Incinerator | NULL         |        11988 |  NULL |
| 129 | Golemagg der Verbrenner  | NULL         |        11988 |  NULL |
| 558 | Majordomo Executus       | NULL         |        12018 |  NULL |
| 240 | Majordomus Exekutus      | NULL         |        12018 |  NULL |
| 554 | Lava Elemental           | NULL         |        12076 |  NULL |
| 212 | Lavaelementar            | NULL         |        12076 |  NULL |
| 564 | Sulfuron Harbinger       | NULL         |        12098 |  NULL |
| 359 | Sulfuronherold           | NULL         |        12098 |  NULL |
| 541 | Firesworn                | NULL         |        12099 |  NULL |
|  90 | Feueranbeter             | NULL         |        12099 |  NULL |
| 557 | Lava Surger              | NULL         |        12101 |  NULL |
| 215 | Lavawoger                | NULL         |        12101 |  NULL |
| 556 | Lava Spawn               | NULL         |        12265 |  NULL |
| 211 | Lavabrut                 | NULL         |        12265 |  NULL |
| 297 | Razzashi Raptor          | NULL         |        14821 |  NULL |
| 301 | Razzashiraptor           | NULL         |        14821 |  NULL |
+-----+--------------------------+--------------+--------------+-------+
44 rows in set (0.23 sec)

如何解决:这个查询可以做我想要的，但是有一个问题，MySQL 无法更新您用于选择子查询的表。

UPDATE `character`
SET `character`.id_old =    (
    SELECT `character`.id
    FROM `character`
    GROUP BY `character`.id_new
    HAVING COUNT(`character`.id_new) > 1
    )
WHERE `character`.id_new IN (
    SELECT `character`.id_new
    FROM `character` 
    GROUP BY `character`.id_new
    HAVING COUNT(`character`.id_new) > 1
    );

所以我必须创建一个包含我需要的所有值的 temp_table:

CREATE TABLE `tmp_character` (
    `id` INT(11) NULL DEFAULT NULL,
    `id_new` INT(11) NULL DEFAULT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

并用值填充它:

INSERT INTO tmp_character (id, id_new)
SELECT id, id_new
FROM `character`
WHERE id_new IN(
    SELECT id_new
    FROM `character` 
    GROUP BY `character`.id_new
    HAVING COUNT(`character`.id_new) > 1
    )
ORDER BY id_new ASC;

现在我得到了这个查询:

UPDATE `character`
SET `character`.id_old =    (
    SELECT tmp_character.id
    FROM tmp_character
    GROUP BY tmp_character.id_new
    HAVING COUNT(tmp_character.id_new) > 1
    )
WHERE `character`.id_new IN (
    SELECT tmp_character.id_new
    FROM tmp_character 
    GROUP BY tmp_character.id_new
    HAVING COUNT(tmp_character.id_new) > 1
    );

现在的问题:错误 1242 (21000):子查询返回超过 1 行确实如此，我有 22 个不同的 id 需要更新，我有 22 个组，不知道为什么它不起作用。

在此查询之后，我的表应如下所示:

+-----+--------------------------+--------------+--------------+-------+
| id  | char_name_de             | char_name_en | id_new       | id_old|
+-----+--------------------------+--------------+--------------+-------+
| 258 | Natter                   | NULL         |         2914 |  258  |
| 342 | Snake                    | NULL         |         2914 |  342  |
| 290 | Rat                      | NULL         |         4075 |  290  |
| 292 | Ratte                    | NULL         |         4075 |  292  |
| 276 | Orakel der Hakkari       | NULL         |        11346 |  276  |
| 152 | Hakkari Oracle           | NULL         |        11346 |  152  |
| 148 | Gurubashi Warrior        | NULL         |        11355 |  148  |
| 201 | Krieger der Gurubashi    | NULL         |        11355 |  201  |
| 344 | Sohn von Hakkar          | NULL         |        11357 |  344  |
| 347 | Son of Hakkar            | NULL         |        11357 |  347  |
+-----+--------------------------+--------------+--------------+-------+
.
.
.

现在“id_new”应该是唯一的，如下所示:

+-----+--------------------------+--------------+--------------+-------+
| id  | char_name_de             | char_name_en | id_new       | id_old|
+-----+--------------------------+--------------+--------------+-------+
| 258 | Natter                   | NULL         |         2914 |  258  |
| 290 | Rat                      | NULL         |         4075 |  290  |
| 276 | Orakel der Hakkari       | NULL         |        11346 |  276  |
| 148 | Gurubashi Warrior        | NULL         |        11355 |  148  |
| 344 | Sohn von Hakkar          | NULL         |        11357 |  344  |
+-----+--------------------------+--------------+--------------+-------+
.
.
.

现在我想删除“id_new”列以获得最终表。

+-------+--------------------------+--------------+-------+
| id    | char_name_de             | char_name_en | id_old|
+-------+--------------------------+--------------+-------+
| 2914  | Natter                   | NULL         |  258  |
| 4075  | Rat                      | NULL         |  290  |
| 11346 | Orakel der Hakkari       | NULL         |  276  |
| 11355 | Gurubashi Warrior        | NULL         |  148  |
| 11357 | Sohn von Hakkar          | NULL         |  344  |
+-------+--------------------------+--------------+-------+
.
.
.

有人可以帮我修改查询以使其运行吗？我需要在突出显示“现在我有了这个查询:”后修复查询，其余的很容易，我自己得到它，我只是将其发布是因为我想在最后得到背景。

Answer 1

这是我的脚本，用于将值从 id 移动到 id_old，其中我有不止一行:

<?php
  function executeQuery($dbConnection, $Query) {
    $result =  $dbConnection -> query($Query);
    return $result;
  }


  function openDB() {
  $dbConnection=  new mysqli($mServer, $mUser, $mPwd, $mDatabase);
    return $dbConnection;
  }

  // open Database
  $DBconnection=openDB();

  // 1st query
  $query="SELECT *
          FROM `character` c
          WHERE c.id_new IN(
            SELECT c.id_new
            FROM `character` c
            GROUP BY id_new
            HAVING COUNT(id_new) > 1
          )
          ORDER BY id_new ASC;";

  // sent query to database
  $result=executeQuery($DBconnection, $query);
  $numRows=$result->num_rows;

  // do as long as you've got rows
  for(;$numRows>0;$numRows--) {

    // go to next row
    $currentRow=$result->fetch_object();

      $id_new=$currentRow->id;
      $query="UPDATE `character` c
              SET c.class=" .$id_new ."
              WHERE c.id IN(" .$id_new .");";
      echo $query ."\n";
      $result2=executeQuery($DBconnection, $query);
   }
  }

?>

这是第二个修改版本，如果一行加倍，它将删除第二行。如果一行增加三倍或更多，它将不起作用!

<?php
  function executeQuery($dbConnection, $Query) {
    $result =  $dbConnection -> query($Query);
    return $result;
  }


  function openDB() {
  $dbConnection=  new mysqli($mServer, $mUser, $mPwd, $mDatabase);
    return $dbConnection;
  }

  // open Database
  $DBconnection=openDB();

  // 1st query
  $query="SELECT *
          FROM `character`
          WHERE id_new IN(
            SELECT id_new
            FROM `character`
            GROUP BY `character`.id_new
            HAVING COUNT(`character`.id_new) > 1
          )
          ORDER BY id_new ASC;";

  // send query to database
  $result=executeQuery($DBconnection, $query);
  $numRows=$result->num_rows;

  // as long as you've got rows
  for(;$numRows>0;$numRows--) {

    // go to next row
    $currentRow=$result->fetch_object();
    for($i=0;$i<2;$i++) {
      if($i==0) {
        // skip 1st line from each row which is doubled
        $currentRow=$result->fetch_object();
      }

      // delete 2nd line from each row which is doubled
      $id_new=$currentRow->id;
      $query="delete from `character`where id=" .$id_new .";";
      $result2=executeQuery($DBconnection, $query);
   }
  }


?>

但正如我所说，这是一个肮脏的脚本!

它是如何工作的？我在第二个查询中使用一个查询的一些值。我保证它可以在没有 php 的情况下工作，但有时一个快速而肮脏的解决方案比需要几天时间的好解决方案更好!

//关闭