如何在 MySQL UPDATE 语句中模拟 OFFSET?
我有一个 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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这基本上说的是
This basically says
我找到了另一个解决方案,它有点超出了我原来问题的范围,但仍然相关。
我已经从找到它们的第一个查询中获得了重复项的计数。我从此计数中减去一,然后按 ID DESC 对 UPDATE 查询进行排序,然后将查询限制为计数减一。这具有相同的目的,并删除除第一个条目之外的所有重复项。
这是我使用的更新查询:
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: