CODEWITHSUNDEEP
phpmysqlsql
James Wilson
James Wilson

Reputation: 809

How to UPDATE rows in one table where there duplictaes in another

I would be massively grateful if someone can help me write a query to update our orders system due to a dupe issue.

We have an orders table and a customers table.

I've identified some duplicate rows in our customers table where the email address and password are the same on unique rows - and are associated with unique live orders by CustomerNumber which exists in each table. This is bad as when a customer logs in to their account, they won't see all of their orders, rather they'll only see the orders associated with the highest customer ID (see login SQL below)

Identify duplicate user accounts:

SELECT
    emailaddress,
    PASSWORD,
    count(*)
FROM
    scustomers
JOIN orders ON orders.customernumber = customers.CustomerNumber
WHERE Completed = 1
GROUP BY
    emailaddress,
    PASSWORD
HAVING
    count(*) > 1

Login SQL:

SELECT * FROM scustomers WHERE EmailAddress = :EmailAddress AND (Password = :Password) ORDER BY CustomerNumber DESC LIMIT 0,1

I need to write a query that:

Where do I start?!

Our system has been fixed so that any customer order a new product by logging in will always use the highest possible associated CustomerNumber so this really is about fixing existing data.

UPDATE:

I've never used SQL Fiddle, but here is some sample data that I hope will help you.. help me!

CUSTOMERS:

customernumber, email, password

3272, [email protected], 9a098e0bade9b4f2ac4ecdf86111cf7e

10001, [email protected], 9a098e0bade9b4f2ac4ecdf86111cf7e

ORDERS:

ordernumber,customernumber,status

123457, 3272, 'LIVE'

123456, 10001, 'LIVE'

I need to update OrderNumber: 123457 to have the CustomerNumber of 10001, not 3272.

Upvotes: 0

Views: 80

Answers (2)

ôkio
ôkio

Reputation: 1790

Step 1 : create a temporary column to store the correct CustomerNumber

ALTER TABLE scustomers
    ADD COLUMN id_tmp INT NOT NULL;

Step 2 : retrieve the correct CustomerNumber

UPDATE scustomers
    INNER JOIN 
        (
        SELECT
            emailaddress,
            PASSWORD,
            MAX(CustomerNumber) AS id
        FROM
            scustomers
        GROUP BY
            emailaddress,
            PASSWORD
        ) AS duplicate ON scustomers.emailaddress = duplicate.emailaddress AND scustomers.PASSWORD = duplicate.PASSWORD
    SET id_tmp = id;

Step 3 : update order table with the correct CustomerNumber

UPDATE orders
    INNER JOIN scustomers ON orders.customerNumber = customers.CustomerNumber
    SET orders.customernumber = id_tmp;

Step 4 : delete duplicate customers

DELETE FROM scustomers
    WHERE customernumber <> id_tmp;

Step 5 : remove the temporary column

ALTER TABLE scustomers
    DROP COLUMN id_tmp;

Upvotes: 1

sasi kanth
sasi kanth

Reputation: 2937

I am just giving idea about sql query you need to write for loop

 SELECT GROUP_CONCAT( customernumber) , i.email
 FROM scustomers i INNER JOIN ( SELECT k.email FROM scustomers k GROUP BY k.email
 HAVING COUNT( `customernumber` ) >1 )j ON i.email = j.email GROUP BY i.email

 Out put :
   ----------------------------
    customernumber    Email 
   ---------------------------
    1,2,3            [email protected]

After this all duplicate will come in on field.explode the new customernumber get last value in that array (Ex: 3 is last value) 

update  `orders` set `customernumber`='last array value(Ex: 3)' WHERE `customernumber` in (customernumber from above  query(1,2,3) )

For delete duplicate records

DELETE n1 FROM scustomers n1, scustomers n2 WHERE n1.customernumber< n2.customernumber AND n1.email= n2.email

Upvotes: 1

Related Questions