SQL Query Advice - Updating records based on duplicates
I'm after some advice for a query I need to write that extends beyond my current SQL skills/capability.
The logic is as follows:
For each set of duplicate devices_fk in SUBQUERY*, update the row(s) with the earliest date_start value to set date_end=NOW(). Only one record should remain, per device_fk, where date_end IS NULL.
SELECT map_id, devices_fk, user_fk, date_start, date_end FROM [users_devices_map] WHERE date_end IS NULL AND devices_fk IN (SELECT devices_fk FROM [users_devices_map] WHERE date_end IS NULL GROUP BY devices_fk HAVING COUNT(*) > 1) ORDER BY devices_fk ASC, date_start DESC
To assist in understanding my question, this is what subquery outputs:
After the above-described query is executed, the subquery should return no results (ie. because there are no duplicate devices_fk with a NULL date_end).
The end goal is to have only one row with a NULL date_end for each devices_fk, by setting an end date (date_end) for the earliest of the start dates (date_start) for that same device (devices_fk).
Thanks in advance for your time
Try this -
;With cteDevicesMap As ( SELECT ROW_NUMBER() OVER (PARTITION BY devices_fk ORDER BY date_start DESC) AS RN FROM [users_devices_map] ) UPDATE [users_devices_map] SET date_end = GETDATE() FROM cteDevicesMap WHERE RN > 1;
Now check records in your table.
SELECT * FROM [users_devices_map] WHERE date_end IS NULL
Have not tested this..
update a set a.date_end = b.date_start from [users_devices_map] a join (select max(map_id) map_id, devices_fk, min(date_start) date_start from [users_devices_map] group by map_id, devices_fk) b on a.map_id <> b.map_id and a.devices_fk = b.devices_fk where a.date_end is null