none
Batch update process help

    Question

  • 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




    • Edited by UOgod619 Saturday, April 26, 2014 12:56 AM Not enough info
    Friday, April 25, 2014 11:38 PM

Answers

  • 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




    Actually... this isn't working either.... any help!? Please!!

    I ended up just reading the Min values into another table and then looking up against it like this:

    select D_ID, P_ID, MIN([Control]) as MinimumControl into TABLE2 from TABLE
    where [Type] = 1 and D_ID in(select D_ID from TABLE where [Type] = 0)
    group by D_ID, P_ID
    
    update t set t.[Control] = c.MinimumControl
    from TABLE t inner join TABLE2 c
    on c.P_ID = t.P_ID and c.D_ID = t.D_ID
    where t.[Type] = 0

    • Marked as answer by UOgod619 Saturday, April 26, 2014 5:27 AM
    Saturday, April 26, 2014 5:26 AM

All replies

  • 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



    • Marked as answer by UOgod619 Saturday, April 26, 2014 2:33 AM
    • Edited by UOgod619 Saturday, April 26, 2014 3:21 AM code was wrong
    • Unmarked as answer by UOgod619 Saturday, April 26, 2014 5:00 AM
    Saturday, April 26, 2014 2:33 AM
  • 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




    Actually... this isn't working either.... any help!? Please!!
    Saturday, April 26, 2014 5:01 AM
  • 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://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, April 26, 2014 5:05 AM
  • 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




    Actually... this isn't working either.... any help!? Please!!

    I ended up just reading the Min values into another table and then looking up against it like this:

    select D_ID, P_ID, MIN([Control]) as MinimumControl into TABLE2 from TABLE
    where [Type] = 1 and D_ID in(select D_ID from TABLE where [Type] = 0)
    group by D_ID, P_ID
    
    update t set t.[Control] = c.MinimumControl
    from TABLE t inner join TABLE2 c
    on c.P_ID = t.P_ID and c.D_ID = t.D_ID
    where t.[Type] = 0

    • Marked as answer by UOgod619 Saturday, April 26, 2014 5:27 AM
    Saturday, April 26, 2014 5:26 AM
  • 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://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    Sorry, I am unsure if this works, but I do appreciate the add. I am working in a production environment and it has been a long day. I would typically test and mark accordingly, but I am too tired to copy all over and test at the moment... need sleep. Thanks I'll check later and update.
    Saturday, April 26, 2014 5:29 AM
  • As for the cursor, change the cursor declaration to

    DECLARE CONTROL_UPDATE CURSOR STATIC LOCAL FOR

    The default cursor type is a dynamic cursor and it can have several unexpected side effects.

    Rather than using SET ROWCOUNT, use UPDATE TOP (1000), as SET ROWCOUNT is deprecated.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, April 26, 2014 10:09 AM