mysql - 选择并更新相同的查询并查找重复项

Question

大家好!我正在尝试编写一个查询来查找内容的重复项，并设置除原始文章之外的所有重复项(原始文章将由计数器定义(如果原始文章的计数器大于所有其他重复项计数器，则它是原始文章) 并在数据库中设置为 active=0。

DB achitecture{id, title (varchar), description(varchar), img(varchar), active(int)}

以下查询用于查找重复项

  SELECT posts.title, posts.counter, posts.id
  FROM posts
  INNER JOIN (
  SELECT title
  FROM posts
  GROUP BY title
  HAVING count(title) > 1
  ) duplicates ON posts.title = duplicates.title
  ORDER BY posts.title;

我需要为重复项设置 active = 0怎么做？

Answer 1

您可以使用JOIN轻松将其转换为UPDATE:

UPDATE posts INNER JOIN
       (SELECT title, MAX(counter) as maxcounter
        FROM posts
        GROUP BY title
        HAVING count(title) > 1
       ) duplicates
       ON posts.title = duplicates.title AND posts.counter < maxcounter
    set posts.active = 0;