Results 1 to 15 of 15

Thread: Update in batch

  1. #1
    Join Date
    Jun 2011
    Posts
    28

    Unanswered: Update in batch

    Its there any way that we can update un batches is sybase? I tried the code below but it looks like the process doest knwo when to stop because its alwais updating 1000 rows and if the max ammount of rows its 1600000 but never gets incremental, it only getd 1000 rows at a time.

    can some help me to correct the code?

    Regards

    Code:
    SET ROWCOUNT 10000
    WHILE (1=1)
        BEGIN 
        UPDATE STUDENTS
        SET NAME = E.NAME
        FROM STUDENTS S,
        EMPLOYES E
        WHERE  S.APP_ID = E.APP_ID
        AND	S.MAY_ID = E.MAY_ID
        IF @@ROWCOUNT != 10000
            BREAK
        END
    SET ROWCOUNT 0

  2. #2
    Join Date
    May 2011
    Posts
    28

    Update in batch

    Hi,
    This SARG will select initial 1000 rows unless one of the SARG value is changed in one of the tables. Hence it doesn't increment. What is the unique key?

    Brs,
    Pradyut

  3. #3
    Join Date
    Jun 2011
    Posts
    28
    Hi

    Its the combination of APP_ID and MAY_ID

    Regards

  4. #4
    Join Date
    May 2011
    Posts
    28

    Update in batch

    Hi,
    Why do you want update in batch? If unique key is combination of APP_ID & MAY_ID then single update statement will work.

    Brs,
    Pradyut

  5. #5
    Join Date
    Jun 2011
    Posts
    28
    Well the problem its that the log get full and at the end the transaction ends in a rollback, i want to do an update with 1600000 records, that its the reason that I want to avoid that the log gets full and I used a similar process when I delete some hughe files. do you have any other suggestion to avoid this?

  6. #6
    Join Date
    May 2011
    Posts
    28

    Update in batch

    Hi,
    Condition (AND S.NAME !=E.NAME) in SARG will increment the update.
    Code:
    SET ROWCOUNT 10000
    WHILE (1=1)
        BEGIN 
        UPDATE STUDENTS
        SET NAME = E.NAME
        FROM STUDENTS S,
        EMPLOYES E
        WHERE  S.APP_ID = E.APP_ID
        AND	S.MAY_ID = E.MAY_ID
        AND S.NAME !=E.NAME
        IF @@ROWCOUNT != 10000
            BREAK
        END
    SET ROWCOUNT 0

  7. #7
    Join Date
    May 2011
    Posts
    28

    Update in batch

    Hi,
    It is better to use begin/commit transaction inside while loop.

  8. #8
    Join Date
    Jun 2011
    Posts
    28
    I am implmenting this solution I will keep you posted and let you know if it work.

    Regards

  9. #9
    Join Date
    Jun 2011
    Posts
    28
    I seems to be working fine, I will do more test. I really apreciate the help

  10. #10
    Join Date
    Jun 2011
    Posts
    28
    I have some questions for the batch process that you help me to resolve, because I did the process and its seems to work, I havet check in the application but I have another table that i need to do a similar process but this one have a size of 70000000 and I dont know which will be the paramenter that will make the process to continue, do I need to use the unique index?? you also mention that I need to use begin transaction and then commit, where shoul I use that? thank you for your advice.

  11. #11
    Join Date
    Jun 2011
    Posts
    28
    By the way on this table I want to update the unique index so I don to know how to move the update all the table. This its the code that I am trying to use

    Code:
    SET ROWCOUNT 10000
    WHILE (1=1)
        BEGIN 		
    	BEGIN TRANSACTION
    	MONEY SET    	
    		ID = E.ID,
    		PHONE = E.PHONE
    	from MONEY M,
    	EMPLOYEES E,
    	RELMONEYEMPLOYEE R,
    	WHERE M.ID = R.ID
    	AND E.ID = R.ID1
    	AND M != R.ID
    	COMMIT TRANSACTION
    	IF @@ROWCOUNT != 10000
                       BREAK
                  END
    SET ROWCOUNT 0

  12. #12
    Join Date
    May 2011
    Posts
    28

    Update in batch

    Hi,
    Yes, unique index is required to avoid any wrong updates.

    I have re-written the query as shown below
    Code:
    declare @rowUpdated int
    select @rowUpdated = 1
    SET ROWCOUNT 10000
    WHILE (@rowUpdated >0)
    BEGIN 
        begin transaction
        UPDATE STUDENTS
        SET NAME = E.NAME
        FROM STUDENTS S,
        EMPLOYES E
        WHERE  S.APP_ID = E.APP_ID
        AND	S.MAY_ID = E.MAY_ID
        AND S.NAME !=E.NAME
        select @rowUpdated= @@ROWCOUNT
        commit transaction     
    END
    SET ROWCOUNT 0
    This code is not tested.Please test it properly in development environment.
    When all rows will be updated @@rowcount will be set to zero because of SARG S.NAME !=E.NAME.

  13. #13
    Join Date
    Jun 2011
    Posts
    28
    I just find that the code that posted its not workin at all the process didnt change anything, so I modify the code to this, but I found some records that werent modified at all.

    SET ROWCOUNT 10000
    WHILE (1=1)
    BEGIN
    BEGIN TRANSACTION
    MONEY SET
    ID = E.ID,
    PHONE = E.PHONE
    from MONEY M,
    EMPLOYEES E,
    RELMONEYEMPLOYEE R,
    WHERE M.ID = R.ID
    AND E.ID = R.ID1
    AND M != R.ID1
    COMMIT TRANSACTION
    IF @@ROWCOUNT != 10000
    BREAK
    END
    SET ROWCOUNT 0

  14. #14
    Join Date
    May 2011
    Posts
    28

    Update in batch

    Hi,
    Can you post the unique index of MONEY ,EMPLOYEES & RELMONEYEMPLOYEE ?
    This query doesn't seems to be same as the first one. First write a select query to check if SARG is returning desired value. Then convert the select statement into update statement. I think SARG in latest post is not returning desired results .

  15. #15
    Join Date
    Jun 2011
    Posts
    28
    The unique for money its id, for employees eid (its a identity) and form relmoneyemployee its rid (its a identity). in the table employee i have name, phone, address, email and relmoneyemployee I have onlu three columns rid, id and id1.

    You are rigth this its not the same query, this its for another table that I need to do the same process and I forgot about to do a select first thank you for the heads up.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •