topic Re: Update Query in Application Development and Automation Discussions

Update Query

Former Member — Sat, 24 Jun 2006 03:50:07 GMT

I need to update a z table from a internal table.There are million entries in the table.

Performance wise which one of the two is better.Please note that I need to update only two fileds of the table dbtab.dbtab has 20 fields.

In the first option i'm pulling only two columns in the second select statment.Which forces me to loop to update.

In the second option i'm pulling all the 20 columns in the second select statment.Which gives me the liberty to update in one statement

Appreicate your help

f1 = key

f2 = field to change

SELECT L1 L2

FROM datbone

INTO TABLE itabone

LOOP AT itabone INTO waone

SELECT f1 f2

INTO TABLE itab

package size 10000

FROM dtab

where f3 = waone-L1.

change the field f2 of itab

ENDSELECT

ENDLOOP.

LOOP AT itab INTO wa

UPDATE dbtab SET f2 = wa-f2

ENDLOOP

In the above only the key and

SELECT L1 L2

FROM datbone

INTO TABLE itabone

LOOP AT itabone INTO waone

SELECT *

INTO TABLE itab

package size 10000

FROM dtab

where f3 = waone-L1.

change the field f2 of itab

ENDSELECT

ENDLOOP.

UPDATE dbtab FROM TABLE itab

Re: Update Query

Former Member — Sat, 24 Jun 2006 05:19:07 GMT

How abt this one?

SELECT L1 L2

FROM datbone

INTO TABLE itabone

if not itabone[] is initial.

SELECT *

FROM dtab

INTO TABLE itab

for all entries in itabone

package size 10000

where f3 = itabone-L1.

endif.

UPDATE dbtab FROM TABLE itab.

reward points if it helps

Regards

Gunjan

Re: Update Query

Former Member — Sat, 24 Jun 2006 06:41:37 GMT

Thanks for your answer.If was wondering since there are million entries is it good to select all the columns of the table?(Since I need to update only one column of the table).Also since there are million entries single update statement will not cause time out?