asked on

for each in SQL

I have a process I inherited that deletes duplicate records

The code is at the bottom

You can see I'm doing it one at a time by manually setting @htid

How can set that for each id in myTable?

DECLARE @htid INT
SET @htid = 70095


DECLARE @tsid INT
SET @tsid = (SELECT ID FROM dbo.TimeSheetFiles WHERE hoursTrackingID = @htid AND verified = 1)

--select
;with c as 
(
	select *, dup = ROW_NUMBER() over ( PARTITION by hoursTrackingID, timesheetID, hsid order by id  ) 
	FROM dbo.HoursTracking_DetailHours WHERE hoursTrackingID = @htid AND timeSheetID = @tsid
)

DELETE from c where dup > 1

Open in new window

Surendra Nath

try the below sql and let us know the results...
by all means backup your data before executing the below.

;with c as 
(
	select *, dup = ROW_NUMBER() over ( PARTITION by hoursTrackingID, timesheetID, hsid order by id  ) 
	FROM dbo.HoursTracking_DetailHours 
	JOIN dbo.TimeSheetFiles
	ON	 verified = 1
	and	 hoursTrackingID = timeSheetID 
)
DELETE from c where dup > 1

Open in new window

Larry Brister

ASKER

Neo_jarvis,
I'll have to try that when I get on location later today

PortletPaul

? deleting from a CTE ? why ?

;with c as
(
      select *, dup = ROW_NUMBER() over ( PARTITION by hoursTrackingID, timesheetID, hsid order by id )
      FROM dbo.HoursTracking_DetailHours
      JOIN dbo.TimeSheetFiles
      ON       verified = 1
      and       hoursTrackingID = timeSheetID
)
SELECT from c where dup = 1
-- just ignore then ??

are we only seeing part of an overall need here?

ASKER CERTIFIED SOLUTION

Thomasian

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Larry Brister

ASKER

That's it
Thanks