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.

Subquery:

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

Answers


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

Need Your Help

Multithreaded code won't compile using g++, but is fine with clang++

c++ multithreading c++11 g++ clang++

I am having trouble with the g++ compiler. On my work machine (running OS X 10.10.4) I was experimenting with some code using Xcode. The code did compile succesfully, and the resulting executable w...