First we need to find the existing orders data which are having duplicate values on column “reference_number”
/* select order which have duplicate reference_number */
SELECT entity_id, reference_number FROM sales_order WHERE reference_number IN (SELECT reference_number FROM sales_order GROUP BY reference_number HAVING count(*) > 1);
/* select max(entity_id) of those duplicate orders with same reference_number */
SELECT * FROM
(
SELECT max(entity_id)
FROM sales_order
WHERE reference_number IS NOT NULL
GROUP BY reference_number HAVING count(*) > 1
) tmp;
/* update those those duplicate orders with same reference_number = reference_number-1 */
UPDATE sales_order
/*SET reference_number = NULL */
SET reference_number=CONCAT(reference_number,'-1')
WHERE entity_id IN
(
SELECT * FROM
(
SELECT max(entity_id)
FROM sales_order
WHERE reference_number IS NOT NULL
GROUP BY reference_number HAVING count(*) > 1
) tmp
);
This way you would be able to update all those duplicate orders with reference_number = reference_number-1
ie reference_number = ORD-1234 will be updated with reference_number = ORD-1234-1
