View previous topic :: View next topic |
Author |
Message |
Catherine Beginner
Joined: 30 Aug 2006 Posts: 32 Topics: 15 Location: Chennai
|
Posted: Tue Feb 20, 2007 2:16 am Post subject: Update on large number of rows |
|
|
I have a table Emp with the data's as below
Emp-id Emp-code
1111 A
2222 A
3333 B
4444 C
5555 B
I have 200 records in this table and I need to update Emp-code of all 200 rows with the code "A".
At present my idea is to put the UPDATE query in a LOOP that runs for 200 times.
UPDATE EMP
SET EMP_CODE = 'A'
WHERE EMP_ID = :EMP-ID
But this would naturally consume more CPU TIME. Mine is an Online module. Is there any way to minimize the CPU time? |
|
Back to top |
|
|
Catherine Beginner
Joined: 30 Aug 2006 Posts: 32 Topics: 15 Location: Chennai
|
Posted: Tue Feb 20, 2007 3:21 am Post subject: Update on large number of rows |
|
|
I may also get greater chance of -911 errors since I'm going to hold the table for a longer time.
The table can contain large no: of rows > 200. I need to update 200 Rows Randomly, I'm given the Emp-id of 200 Rows |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
|
Posted: Tue Feb 20, 2007 10:42 am Post subject: |
|
|
Catherine,
try this
Code: |
UPDATE EMP
SET EMP_CODE = 'A'
WHERE EMP_ID IN (:emp-id1
,:emp-id2
,:emp-id3
...
,:emp-id200) ;
|
Hope this helps...
Cheers
Kolusu _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
Catherine Beginner
Joined: 30 Aug 2006 Posts: 32 Topics: 15 Location: Chennai
|
Posted: Thu Feb 22, 2007 1:49 am Post subject: Update on large number of rows |
|
|
Kolusu,
If in the EMP table, apart from the EMP-ID I have another one coloumn EMP-IND as the Primary Key ( My table has combination of EMP-ID and EMP-IND as Primary key) . Is there any possibility that I can give a similar Multiple Row update as you mentioned , in this case?
Also are there any disadvantages to Updating Multiple rows at one stretch? |
|
Back to top |
|
|
programmer1 Beginner
Joined: 18 Feb 2004 Posts: 138 Topics: 14
|
Posted: Thu Feb 22, 2007 10:22 am Post subject: |
|
|
Quote: |
I have 200 records in this table and I need to update Emp-code of all 200 rows with the code "A"
|
If you want to update all the records, why not use a simple update as:
UPDATE EMP
SET EMP_CODE = 'A' _________________ Regards,
Programmer |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
|
Posted: Thu Feb 22, 2007 10:43 am Post subject: |
|
|
Quote: |
If in the EMP table, apart from the EMP-ID I have another one coloumn EMP-IND as the Primary Key ( My table has combination of EMP-ID and EMP-IND as Primary key) . Is there any possibility that I can give a similar Multiple Row update as you mentioned , in this case?
|
yes you can you just need to concatenate both fields(assuming both columns are of the same type . If you use the full primary key then it is more efficient as it will use the INDEX.
try this
Code: |
UPDATE EMP
SET EMP_CODE = 'A'
WHERE (EMP_ID || EMP-IND) IN (:emp-id1 || :emp-ind1
,:emp-id2 || :emp-ind2
,:emp-id3 || :emp-ind3
,:emp-id4 || :emp-ind4
.....
,:emp-id200 || :emp-ind200);
|
Hope this helps...
Cheers
Kolusu
at any cost 200 rows update is not a big deal unless the WHERE clause results in a tablespace scan.
Hope this helps...
Cheers
Kolusu _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
Catherine Beginner
Joined: 30 Aug 2006 Posts: 32 Topics: 15 Location: Chennai
|
Posted: Fri Feb 23, 2007 4:04 am Post subject: Update on large number of rows |
|
|
Kolusu,
I have one key with data type CHAR and another key with SMALLINT |
|
Back to top |
|
|
Catherine Beginner
Joined: 30 Aug 2006 Posts: 32 Topics: 15 Location: Chennai
|
Posted: Fri Feb 23, 2007 6:38 am Post subject: Update on large number of rows |
|
|
Kolusu,
I'm not able to use concatenate operator
Here is wat I got while executing in SPUFI
UPDATE TDB2TRAM.ABBT_ABBR_TRD
SET LOCK_IN = 'Y'
WHERE (TRD_REF_ID |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12372 Topics: 75 Location: San Jose
|
Posted: Fri Feb 23, 2007 7:45 am Post subject: |
|
|
Quote: |
you just need to concatenate both fields(assuming both columns are of the same type
|
Catherine ,
Did you overlook that statement of mine ?
Secondly the error is because you coded a Broken pipe symbol for concatenation Symbol which is wrong.
Try this
Code: |
UPDATE EMP
SET EMP_CODE = 'A'
WHERE (EMP_ID || CHAR(EMP-IND)) IN (:emp-id1 || :emp-ind1
,:emp-id2 || :emp-ind2
,:emp-id3 || :emp-ind3
,:emp-id4 || :emp-ind4
.....
,:emp-id200 || :emp-ind200);
|
Hope this helps...
Cheers
Kolusu _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
Catherine Beginner
Joined: 30 Aug 2006 Posts: 32 Topics: 15 Location: Chennai
|
Posted: Tue Feb 27, 2007 9:27 am Post subject: Update on large number of rows |
|
|
Kolusu,
Thanks a lot for ur information and time
Catherine. |
|
Back to top |
|
|
|
|