View previous topic :: View next topic |
Author |
Message |
ranjit5311 Beginner
Joined: 27 Nov 2005 Posts: 27 Topics: 14
|
Posted: Wed Aug 29, 2007 6:23 am Post subject: Query to update the current value |
|
|
Hi All,
There are two tables;-
TAB1(LAST_KEY, UPDATED_BY)
TAB2(ADDRESS_ID,UPDATED_BY)
LAST_KEY is a dynamic field. The value keeps on increasing. My requirement is to update the LAST_KEY to current value + 1. And this valuse should be updated in TAB2(ADDESS_ID).
I believe this can be achive in following way:-
1. lock table TAB1
2. Fetch the current value of LAST_KEY
3. update this LAST_KEY by 1
4. update the value in TAB2(ADDRESS_ID)
5.Release the lock TAB1.
But How the above steps cab be done in Spufi.
Please help me out to solve my query.
Thanks,
Ranjit Kumar _________________ One should move lightly through the life, carrying no excess baggage. |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
|
Posted: Wed Aug 29, 2007 7:12 am Post subject: |
|
|
ranjit5311,
Try this
Code: |
INSERT INTO TAB1
(SELECT MAX(A.LAST_KEY) + 1
,CHAR('RANJIT')
FROM TAB1 A
WHERE NOT EXISTS ( SELECT LAST_KEY
FROM TAB1 B
WHERE B.LAST_KEY = A.LAST_KEY + 1));
|
Code: |
INSERT INTO TAB2
(SELECT MAX(LAST_KEY)
,CHAR('RANJIT')
FROM TAB1);
|
Hope this helps...
Cheers
Kolusu _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
videlord Beginner
Joined: 09 Dec 2004 Posts: 147 Topics: 19
|
Posted: Thu Aug 30, 2007 9:55 am Post subject: |
|
|
LOCK TASBLE TAB1 IN EXCLUSIVE MODE;
UPDATE TAB1 SET LAST_KEY = LAST_KEY + 1 WHERE ...
UPDATE TAB2 ....
COMMIT;
before the COMMIT statement, the table TAB1 is locked, no other DB2 thread can change the data in the table. |
|
Back to top |
|
|
|
|