如何在 MySQL UPDATE 语句中模拟 OFFSET?

发布于 2024-12-29 23:11:39 字数 577 浏览 1 评论 0原文

我有一个 MySQL 数据库表,其中包含文章 ID(主键)和文章标题。我想从表中删除重复的标题,但保留第一次出现的标题。我最初只是对所有重复的标题进行了查询:

SELECT
    title,
    count( id ) AS count
FROM articles
GROUP BY title
HAVING count > 1

然后我使用 foreach 循环和此命令将所有重复的标题替换为空白:

UPDATE articles
SET title = ''
WHERE title = '$duplicate_title'

我想更新 articles 表并替换所有重复的标题除了第一个条目之外,它基于使用类似这样的内容的文章 ID ASC。问题是 OFFSET 似乎在 UPDATE 中不起作用。有没有办法在单个查询中做到这一点?

UPDATE articles
SET title = ''
WHERE title = '$duplicate_title'
ORDER BY id ASC
OFFSET 1

I have a MySQL database table that contains an Article ID ( primary key ) and an Article Title. I want to remove duplicate titles from the table, but keep the first occurrence of the title. I initially simply did a query for all duplicate titles:

SELECT
    title,
    count( id ) AS count
FROM articles
GROUP BY title
HAVING count > 1

Then I replaced all the duplicate titles with a blank using a foreach loop and this command:

UPDATE articles
SET title = ''
WHERE title = '$duplicate_title'

I'd like to update the articles table and replace all duplicate titles except the first entry, based on the Article ID ASC using something like this. The problem is that OFFSET doesn't seem to work in an UPDATE. Is there a way to do this in a single query?

UPDATE articles
SET title = ''
WHERE title = '$duplicate_title'
ORDER BY id ASC
OFFSET 1

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

彩扇题诗 2025-01-05 23:11:39
UPDATE articles a
INNER JOIN articles b
    ON a.title = b.title AND a.ID > b.ID
SET title = '';

这基本上说的是

更新所有存在具有相同标题和较低 ID 的匹配文章的文章

UPDATE articles a
INNER JOIN articles b
    ON a.title = b.title AND a.ID > b.ID
SET title = '';

This basically says

update all articles where there exists a matching article with the same title and a lower ID

对你而言 2025-01-05 23:11:39

我找到了另一个解决方案,它有点超出了我原来问题的范围,但仍然相关。

我已经从找到它们的第一个查询中获得了重复项的计数。我从此计数中减去一,然后按 ID DESC 对 UPDATE 查询进行排序,然后将查询限制为计数减一。这具有相同的目的,并删除除第一个条目之外的所有重复项。

这是我使用的更新查询:

UPDATE articles
SET title = ''
WHERE title = '$duplicate_title'
ORDER BY id DESC
LIMIT $duplicate_count_minus_one

I found another solution that was a little outside the scope of my original question, but relevant nonetheless.

I already had a count of duplicates from the first query that found them. I subtracted one from this count, then ordered my UPDATE query by ID DESC and then LIMITed the query to the count minus one. This serves the same purpose and removes all duplicates except for the first entry.

Here is the UPDATE query I use:

UPDATE articles
SET title = ''
WHERE title = '$duplicate_title'
ORDER BY id DESC
LIMIT $duplicate_count_minus_one
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文