CODEWITHSUNDEEP
mysqlsql
Kevin
Kevin

Reputation: 359

MySQL Mark Records with Duplicates

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.

table customers (Order number '123 is duplicate)

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

desired, after update ()

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

The select works perfectly:

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

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?

Upvotes: 3

Views: 1009

Answers (2)

Jack
Jack

Reputation: 435

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.

Not tested but should work 

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 :)

Upvotes: 3

Evgeny
Evgeny

Reputation: 4010

You use of JOIN statement is invalid. Try this:

UPDATE `customers` c
       INNER JOIN (SELECT `ordernumber`
                   FROM   `customers`
                   GROUP  BY `ordernumber`
                   HAVING Count(`ordernumber`) > 1) jn
               ON jn.ordernumber = c.ordernumber
SET    c.working = 'DUPL'

Upvotes: 1

Related Questions