vallishar
Beginner
Joined: 17 Dec 2002
Posts: 53
Topics: 14
Location: BengaLuru
|
 Posted: Wed Dec 18, 2002 6:25 pm Post subject: Subselect in the update statement - DB2V7 |
 |
|
Hi
I am using DB2 V7 on OS-390.
This version of DB2 provides the facility of a subselect in an Update statement,
which is not allowed in the earlier version.
I have tried to execute a step like this:
The following gives the table structures and sample values which I am using.
| Code: |
TableA
---------------------
Col1A Col2A Timestamp
AAA Y
BBB N
CCC Y
TableB
---------------------------
Col1B Col2B Col3B Col4B Timestamp
AAA Y BBB Y
CCC Y AAA Y
BBB Y CCC Y
AAA Y CCC Y
|
The update query looks like this:
The values in the update statement are those at the
time of execution.
| Code: |
UPDATE TABLEA A
SET COL2A = 'Y',
Timestamp =
CURRENT TIMESTAMP
WHERE COL1A = 'AAA'
AND COL1A NOT IN
(SELECT B.COL1B FROM
TABLEB B , TABLEA C
WHERE B.COL1B = 'AAA'
AND B.COL2B = 'Y'
AND B.COL3B = C.COL1A
AND B.COL4B = C.COL2A )
|
If the table has the data as above, the update would be successful.
| Code: |
TableA
---------------------
Col1A Col2A Timestamp
AAA Y
BBB Y
CCC Y
TableB
---------------------------
Col1B Col2B Col3B Col4B Timestamp
AAA Y BBB Y
CCC Y AAA Y
BBB Y CCC Y
AAA Y CCC Y
|
If the data were to be like this, then the update would have failed.
But, prior to execution, if the data were :
| Code: |
TableA
---------------------
Col1A Col2A Timestamp
AAA N
BBB N
CCC Y
TableB
---------------------------
Col1B Col2B Col3B Col4B Timestamp
AAA Y BBB Y
CCC Y AAA Y
BBB Y CCC Y
AAA Y CCC Y
|
And if two different instances of the same program were being executed
one, to update the row with value 'AAA' to 'Y' and another, to update the row with value 'BBB' to 'Y'.
Ideally speaking, the update should have been failed if the updates have occured on different rows were to be seen by the subselect. But it didnt happen. Both the updates were successful.
The DB2 V7 manual says that it makes use of temporary files while performing the subselect on the update.
Can anybody throw some light on :
1. How does the Sub-select work when used in the Update statement like this ?
2. Why did the update as coded above was successful for both the instances ?
3. How will the SQL behave if the sub-select didnt have a join on another table, but may have a sub-select on only the table being updated (Would the result be the same as this or not) ?
Vallish |
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
