MySql row_number() fix duplicates

Trying to add a new unique key to a table and on of the fields allows nulls and there are already duplicate records that need to be adjusted now. This is the perfect case for using row_number to order the ids

Here is an example table

create table LocationApplicability
(
    Id                        int unsigned auto_increment   primary key,
    LocationId                int unsigned                  not null,
    StartDate                 datetime                      null,
    IsApplyUnique             bit                           null,
    CreateDate                datetime                      not null    DEFAULT CURRENT_TIMESTAMP
)

What I need to do is use the row_number() function to group the duplicate records by LocationId, IsApplyUnique and then order them by CreateDate to get the row_number ids to generate in order as latest duplicate record will have the highest rank order.

Came up with this sql expression leveraging a Common Table Expression to do it

WITH duplicates as (select Id,
                           LocationId,
                           ROW_NUMBER() OVER (PARTITION BY LocationId, IsApplyUnique ORDER BY CreateDate) AS RowId
                    from LocationApplicability
                    where StartDate IS NULL)
SELECT d.Id, d.LocationId, d.RowId
FROM duplicates d
where d.RowId > 1;

This can be use as part of an update or sent to temporary table to individually adjust the records.

In this case create a separate table for auditing and notification, fill, then adjust the original records, and finally create the new unique key to prevent future duplicates like so

CREATE TABLE DuplicateLocationIds (
    Id int unsigned unique NOT NULL,
    LocationId int unsigned NOT NULL,
    RowId int unsigned NOT NULL
);

INSERT INTO DuplicatePropertyLocationIds (Id, LocationId, RowId)
WITH duplicates as (select Id,
                           LocationId,
                           ROW_NUMBER() OVER (PARTITION BY LocationId, IsApplyUnique ORDER BY CreateDate) AS RowId
                    from LocationApplicability
                    where StartDate IS NULL)
SELECT d.Id, d.LocationId, d.RowId
FROM duplicates d
where d.RowId > 1;

UPDATE LocationApplicability p
    inner join DuplicatePropertyLocationIds d
    on p.Id = d.Id
SET p.StartDate = DATE_ADD('1900-01-01', interval d.RowId - 1 DAY)
WHERE p.StartDate IS NULL;

alter table LocationApplicability
    add unique index UQ_LocationApplicability (LocationId, StartDate, IsApplyUnique);