Batch update process help

Please help!!! Beating my head against a wall right now and not seeing what I'm doing wrong... Only the cursor is/has updated records, but it only updated ~180k out of ~435k records. The other two seem to just update the same batch over and over, which isn't making sense to me as I have specified a criteria ([Control] is null) that I am changing (updating [Control]). Please help me figure out where/what I am missing... Here are some blocks I've tried:

DECLARE @FILE INTEGER
DECLARE @DID INTEGER
DECLARE @PID INTEGER

DECLARE CONTROL_UPDATE CURSOR FOR
        select [F_ID], D_ID, P_ID
        from TABLE
        where [Type] = 0 and [Control] is null
FOR UPDATE OF [Control]

OPEN CONTROL_UPDATE
FETCH NEXT FROM CONTROL_UPDATE into @FILE, @DID, @PID

WHILE @@FETCH_STATUS = 0
        BEGIN
                update f1 set f1.[Control] = (select MIN(f2.[Control]) from TABLE f2 where f2.[Control] is not null and f2.[Type] = 1 and f2.D_ID = @DID and f2.P_ID = @PID)
                from TABLE f1
                where [F_ID] = @FILE

                FETCH NEXT FROM CONTROL_UPDATE into @FILE, @DID, @PID
        END

CLOSE CONTROL_UPDATE
DEALLOCATE CONTROL_UPDATE

SET ROWCOUNT 1000

WHILE(1 = 1)
        BEGIN
                BEGIN TRANSACTION

                update f1 set f1.[Control] = (select MIN(f2.[Control]) from TABLE f2 where f2.[Type] = 1 and f2.[Control] is not null and f2.D_ID = f1.D_ID and f2.P_ID = f1.P_ID)
                from TABLE f1
                where f1.[Control] is null and f1.[Type] = 0

                IF @@ROWCOUNT = 0
                        BEGIN
                                COMMIT TRANSACTION

                                BREAK
                        END

                COMMIT TRANSACTION
        END

SET ROWCOUNT 0

update f1 set f1.[Control] = 
(select MIN(f2.[Control]) from TABLE f2 where
f2.[Control] is not null and f2.[Type] = 1 and f2.D_ID = f1.D_ID and f2.P_ID = f1.P_ID)
from TABLE f1
where f1.[Type] = 0 and f1.[Control] is null

Basically I am doing this as leg work to link records I moved between databases. The next step will be to update the D_ID to the new value in the new database by using the updated [Control]. I already have records in my table that are updated correctly (Type = 1) that have the correct [Control] value. I now need to grab the lowest [Control] value from documents in the same table that have the same D_ID and P_ID value as the records with [Type] = 0 and update the [Control] equal to it for them. This is a table that consists of over 50 million lines and thus I am trying to do in batches. Not having much luck and the problem is escaping me at the moment... if someone is seeing it and willing to share... I will give you a huge virtual hug! ;-D

Figured it out...

update t set t.[Control] = (select MinimumControl from 
(select D_ID, P_ID, MIN([Control]) as MinimumControl from TABLE
where [Type] = 1 and D_ID in(select D_ID from TABLE where [Type] = 0)
group by D_ID, P_ID) subquery
where
subquery.P_ID = t.P_ID
and subquery.D_ID = t.D_ID)
from TABLE t
where t.[Type] = 0

The above update can be simplified as this

update t 
set t.[Control] = MinimumControl 
from
(
select MIN(CASE WHEN Type = 1 THEN [Control] END) OVER (PARTITION BY D_ID,P_ID) AS MinimumControl,[Control]
FROM Table
WHERE Type = 0
)t
WHERE MinimumControl IS NOT NULL

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://ift.tt/19nLNVq http://ift.tt/1iEAj0c