CODEWITHSUNDEEP
mysqlduplicatessql-update
Oliver
Oliver

Reputation: 105

Need help updating records with duplicate values in MySQL

I have a mysql - table, representing orders that are being synced to my db by a complex sync-process from a rest api - due to an error, I now have duplicates and I am not familiar enough with mysql to fix this properly - and I would love to avoid the "php-way", that I could do myself.

A duplicate record in my table has a specific column with the same order id - and another column named "record_state", that represents the status of the record (1 = newest record, 2 = older record) I know how to find the duplicates with the following statement:

SELECT 
orderid, 
COUNT(orderid)
FROM
orders
WHERE `time_get` > 1584226800 AND `system` = 'live' and `record_state` = 1 and `hidden` = 0 AND `deleted` = 0
GROUP BY orderid
HAVING COUNT(orderid) > 1

What I need to do now, is update any older duplicate of these results (e.g. count(orderid) = 2). I have a time column, that I could use to compare them. The main objective for me is to have only the newest orders have a record_state = 1, the older duplicates shall get record_state = 2. I`d be happy about any help - this is a live production table of my company, so I absolutely mustnt mess this up. Thanks in advance, Oliver

Upvotes: 0

Views: 31

Answers (1)

Akina
Akina

Reputation: 42844

UPDATE orders o1
SET o1.record_state = 2
WHERE ( all conditions except record_state ) 
  AND o1.record_state != 2
  AND EXISTS ( SELECT NULL
               FROM orders o2
               WHERE o1.orderid = o2.orderid
                 AND o1.time_get < o2.time_get
                 AND o2.record_state = 1 )

I.e. we update all records with record_state != 2 if exists more recent record with the same orderid and record_state = 1.

mysql says : usage of o1 in the FROM-clause part is not allowed

If so try

UPDATE orders o1, orders o2
SET o1.record_state = 2
WHERE ( all conditions except record_state by o1 ) 
  AND o1.record_state != 2
  AND o1.orderid = o2.orderid
  AND o1.time_get < o2.time_get
  AND o2.record_state = 1;

Upvotes: 1

Related Questions