CODEWITHSUNDEEP
sqlsql-serversql-server-2008
tpag26
tpag26

Reputation: 81

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:

Output of Subquery

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

Upvotes: 1

Views: 50

Answers (2)

Jose Tuttu
Jose Tuttu

Reputation: 428

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

Upvotes: 1

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

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

Upvotes: 2

Related Questions