根据日期和状态在MYSQL中重复

This is my current query to find duplicate phone numbers.

这是我当前查询重复的电话号码。

SELECT 
    id, cust_num, entry_date 
FROM 
    nums 
GROUP BY 
    cust_num 
HAVING 
    count(*) >= 2 
ORDER BY 
    id 
DESC

However, now Im looking to update them all in one go, based on criteria.

但是,现在我希望根据标准一次性更新它们。

I only want to update the newer ones with higher id's than the original. And only if they have a certain status.

我只想更新id比原版更高的新版本。并且只有他们有一定的地位。

Heres an example, of what I would update based on a list of duplicates pulled from database.

下面是一个例子,我会根据从数据库中提取的重复项列表进行更新。

 ID |  Num | date    | status
  1    555    Sep-12   NEW    (this row wouldnt update first instance of 555)
  33   555    Oct-12   NEW    (this row would update, duplicate with NEW as status)
  42   333    Dec-12   NEW    (this row wouldn't update first instance of 333)
  5    555    Jan-13   ACTIVE (this row wouldnt update, duplicate but wrong status)
  66   333    Feb-14   NEW    (this row would update, duplicate with NEW as status)
  6    555    Jan-13   NEW    (this row would update, duplicate with NEW as status)
  77   333    Mar 15   ACTIVE (this row wouldnt update, duplicate but wrong status)

So the real question is, what query would I use to pull all the duplicates like this, and then update them based on their status.

所以真正的问题是,我将使用什么查询来拉出这样的所有重复项,然后根据它们的状态更新它们。

2 个解决方案

#1

UPDATE nums n SET ... WHERE n.status='NEW' AND (select count(*) from nums where num = n.num and id < n.id and status = 'NEW') > 0;

Add in your SET statement for whatever you want to update.

在SET语句中添加要更新的内容。

#2

Here is the select. and the link to fiddle

这是选择。和小提琴的链接

select * from mytable as m where `status` = 'NEW' and exists 
    (select * from mytable as mm where mm.`id` < m.`id` and mm.`num` = m.`num`)

Update status to UPDATED

将状态更新为UPDATED

update mytable as m set m.`status` = 'UPDATED' where `status` = 'NEW' and exists 
(select * from mytable as mm where mm.`id` < m.`id` and mm.`num` = m.`num`)

#1

UPDATE nums n SET ... WHERE n.status='NEW' AND (select count(*) from nums where num = n.num and id < n.id and status = 'NEW') > 0;