phpmyadmin更新m到n关系表以从单独的表中删除重复项

Question

我有两张桌子。

CREATE TABLE designs
    ( game_id       INT     NOT NULL,
      des_id        INT     NOT NULL,
      PRIMARY KEY(game_id, des_id),
      FOREIGN KEY(game_id) REFERENCES Game(id),
      ON UPDATE CASCADE)

CREATE TABLE designer
    ( name      VARCHAR(30)     NOT NULL,
      id        INT             NOT NULL,
      PRIMARY KEY(id),
      FOREIGN KEY(id) REFERENCES designs(des_id),
      ON UPDATE CASCADE);    

让我说我有数据：

设计：

0--0

0--1

1-2

2-3

2-4

..

设计师：

鲍勃--0

吉尔-1

鲍勃--2

罗伯-3

吉尔-4

在更新之后，我希望“设计”表看起来像：

0--0

0--1

1--0

2-3

2-1

我需要什么更新查询来完成这个任务？

我尝试过的一些查询是：

UPDATE designs
SET des_id = (
SELECT a.id
FROM designer as a
JOIN designer as b
ON a.name=b.name AND a.id < b.id
WHERE des_id = b.id);    

..。

UPDATE `designs` as a
JOIN designer as b
ON a.des_id=b.id
SET a.des_id = b.id
WHERE b.id = (
SELECT c.id
FROM designer as c
LEFT JOIN designer as d
ON c.name=d.name
WHERE c.id<d.id)

Answer 1

我有个主意。请注意，它使用了以“group/order”的形式进行的有记录的黑客攻击：

UPDATE designs d 
 JOIN 
    ( select d1.id matcher_id 
           , d2.id select_id 
        from `designer` d1  
        JOIN designer d2 
          ON d1.name = d2.name 
       group 
          by d1.id 
       Order 
          by d2.id
    ) x  
   ON x.matcher_id = d.des_id 
  SET d.des_id = select_id

Answer 2

你的左加入想法几乎是正确的，但这里有另一个更快的想法.

 DROP TABLE IF EXISTS designs;

 CREATE TABLE designs
     ( game_id INT NOT NULL 
     , designer_id INT NOT NULL
     , PRIMARY KEY(game_id, designer_id)
     );

 DROP TABLE IF EXISTS designers;

 CREATE TABLE designers
     ( name VARCHAR(30) NOT NULL
     , designer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
     );

 INSERT INTO designs VALUES 
 (1,1),
 (1,2),
 (2,3),
 (3,4),
 (3,5);

 INSERT INTO designers VALUES 
 ('Bob',1),
 ('Jill',2),
 ('Bob',3),
 ('Rob',4),
 ('Jill',5);

 SELECT * FROM designs;
 +---------+-------------+
 | game_id | designer_id |
 +---------+-------------+
 |       1 |           1 |
 |       1 |           2 |
 |       2 |           3 |
 |       3 |           4 |
 |       3 |           5 |
 +---------+-------------+

 SELECT * FROM designers;
 +------+-------------+
 | name | designer_id |
 +------+-------------+
 | Bob  |           1 |
 | Jill |           2 |
 | Bob  |           3 |
 | Rob  |           4 |
 | Jill |           5 |
 +------+-------------+

 UPDATE designs g
   JOIN designers d
     ON d.designer_id = g.designer_id
   JOIN designers x ON x.name = d.name
   JOIN
      ( SELECT name
             , MIN(designer_id) min_designer_id
          FROM designers
         GROUP
            BY name
      ) y
     ON y.name = x.name
    AND y.min_designer_id = x.designer_id
    SET g.designer_id = x.designer_id;

 SELECT * FROM designs;
 +---------+-------------+
 | game_id | designer_id |
 +---------+-------------+
 |       1 |           1 |
 |       1 |           2 |
 |       2 |           1 |
 |       3 |           2 |
 |       3 |           4 |
 +---------+-------------+

实际上，在更新的特殊情况下，我认为这也同样有效，我不太确定这是否会降低性能.

UPDATE designs g
  JOIN designers x
    ON x.designer_id = g.designer_id
  JOIN designers y
    ON y.name = x.name
   AND y.designer_id < x.designer_id
   SET g.designer_id = y.designer_id;