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

Question

我有 2 张 table 。

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 by/order by”技巧形式的记录黑客:

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