Update in batch

eldeleon · #1 (**permalink**) 09-05-11, 18:17

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

pradyut.dhara · #2 (**permalink**) 09-06-11, 02:27

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

eldeleon · #3 (**permalink**) 09-06-11, 10:46

Hi

Its the combination of APP_ID and MAY_ID

Regards

pradyut.dhara · #4 (**permalink**) 09-06-11, 12:37

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

eldeleon · #5 (**permalink**) 09-06-11, 12:55

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?

pradyut.dhara · #6 (**permalink**) 09-06-11, 13:14

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

pradyut.dhara · #7 (**permalink**) 09-06-11, 13:29

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

eldeleon · #8 (**permalink**) 09-07-11, 11:10

I am implmenting this solution I will keep you posted and let you know if it work.

Regards

eldeleon · #9 (**permalink**) 09-07-11, 11:41

I seems to be working fine, I will do more test. I really apreciate the help

eldeleon · #10 (**permalink**) 09-08-11, 11:39

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.

eldeleon · #11 (**permalink**) 09-08-11, 11:51

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

pradyut.dhara · #12 (**permalink**) 09-08-11, 12:12

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.

eldeleon · #13 (**permalink**) 09-08-11, 12:17

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

pradyut.dhara · #14 (**permalink**) 09-08-11, 12:28

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 .

eldeleon · #15 (**permalink**) 09-08-11, 12:44

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech