MySQL 用重复标记记录 [英] MySQL Mark Records with Duplicates

问题描述

我有一个名为客户的 MYSQL 表.其中一些在 orderNumber 上有重复的条目.
我想在所有这些记录中输入 DUPL 的值.在所有这些记录中工作，而不仅仅是额外的副本.请注意，fieldA 和fieldB 中的值可能不会重复，因此我需要全部标记.

I have a MYSQL table named customers. Some of these have duplicate entries on orderNumber.
I'd like to enter the value of DUPL in customers.working in all of these records, not just the extra copy. Note that the values in fieldA and fieldB may not be duplicates, so I need to mark all.

表客户(订单号 '123 重复)

table customers (Order number '123 is duplicate)

orderNumber     fieldA      fieldB     working
123             vdss        fhghg
456             bhtf        fhbb
789             goyh        vhhgg
123             fhgh        fhfhf

需要，更新后()

orderNumber     fieldA      fieldB     working
123             vdss        fhghg      DUPL
456             bhtf        fhbb 
789             goyh        vhhgg
123             fhgh        fhfhf      DUPL

选择工作完美:

SELECT `orderNumber` 
FROM `customers` 
GROUP BY `orderNumber` 
HAVING COUNT(`orderNumber`) > 1

但更新没有.我有一个错误，MYSQL 无法使用选择更新:

But the update does not. I have an error that MYSQL cannot use the select to update:

UPDATE `customers` set `customers.working` ='DUPL' where `customers.orderNumber` = 
(SELECT `orderNumber` 
FROM `customers` 
GROUP BY `orderNumber` 
HAVING COUNT(`orderNumber`) > 1);

我也试过这个，但得到一个语法错误:

I also tried this, but get a syntax error:

UPDATE `customers` SET `customers.working` ='DUPL' 
WHERE customers.orderNumber = INNER JOIN 
(SELECT `orderNumber` 
FROM `customers` 
GROUP BY `orderNumber` 
HAVING COUNT(`orderNumber`) > 1);

格式化的正确方法是什么?

What is the correct way to format this?

推荐答案

这是凭记忆得出的，未经测试.由于您没有说明错误是什么，我正在猜测之前对 Mysql 做过类似的事情.如果错误提示您无法从同一个表中更新和选择，则需要为选择添加别名.如果没有，我认为您应该使用 IN，而不是连接.

This is from memory and not tested. As you have not said what the error was, I am making a guess from doing similar before with Mysql. If the error is saying you cannot update and select from the same table, you need to add an alias to the select. If not, I think you should use an IN, not a join.

未测试但应该可以使用

UPDATE `customers`
SET    `customers.working` = 'DUPL'
WHERE  customers.ordernumber IN (SELECT t1.`ordernumber`
                                 FROM   (SELECT ordernumber
                                         FROM   `customers`
                                         GROUP  BY `ordernumber`
                                         HAVING Count(`ordernumber`) > 1) AS t1);

在手机上，希望格式正确，括号按正确顺序等:)

On a mobile so hopefully formatted ok and brackets in the correct order etc :)

这篇关于MySQL 用重复标记记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！