questionable results with

John P. Schulting

questionable results with
I received the following question from one of our more experienced
developers and was wondering if anyone else had come across something
similar:

Note: We are at DB2 V6 on OS390.

I was trying make use of the recently(?) available capability to Set a
column equal to a value acquired via a subselect. It didn't work quite the
way I expected so I was wondering if there was any additional information
or safety tips that could be provided in its use.

Here is my query:

UPDATE TABLEA A
SET A.COL1 =
(SELECT B.COL1
FROM TABLEB B
WHERE A.COL2 = B.COL2)

Not all of the rows on TABLEA had matching rows on TABLEB and I thought
that the non-matching rows would not be updated. However, I checked the
data after running the query and DB2 had placed residual values into the
updated column for those rows where the sub-select did not find a match.
Basically, DB2 updated every row in TABLEA and when the sub-select failed
to find a matching row on TABLEB, it just re-used the B.COL1 value from the
last time it had found a matching row. I can sort of understand why DB2
updated every row, but I would have expected it to use Null or a default
value when the sub-select found no match.

Another person in my area wrote a similar query, but did not encounter the
same problem. The differences we noticed were that neither of the columns
being matched on in my sub-select were part of the tables' primary keys
whereas my co-workers sub-select matched on the primary keys of both tables
involved.

I re-wrote my query as shown below and got the desired results:

UPDATE TABLEA A
SET A.COL1 =
(SELECT B.COL1
FROM TABLEB B
WHERE A.COL2 = B.COL2)
WHERE EXISTS
(SELECT *
FROM TABLEB C
WHERE A.COL2 = C.COL2)



Dave Nance

Re: questionable results with
(in response to John P. Schulting)
You are updating many More rows than you care to, due to not qualifying what rows in table a to update. I.E.:
UPDATE TABLEA A
SET A.COL1 =
(SELECT B.COL1
FROM TABLEB B
WHERE A.COL2 = B.COL2)
WHERE A.C? =?????


Dave Nance
First Health Services, Corp.
(804)527-6841

>>> [login to unmask email] 01/10/01 01:53PM >>>
I received the following question from one of our more experienced
developers and was wondering if anyone else had come across something
similar:

Note: We are at DB2 V6 on OS390.

I was trying make use of the recently(?) available capability to Set a
column equal to a value acquired via a subselect. It didn't work quite the
way I expected so I was wondering if there was any additional information
or safety tips that could be provided in its use.

Here is my query:

UPDATE TABLEA A
SET A.COL1 =
(SELECT B.COL1
FROM TABLEB B
WHERE A.COL2 = B.COL2)

Not all of the rows on TABLEA had matching rows on TABLEB and I thought
that the non-matching rows would not be updated. However, I checked the
data after running the query and DB2 had placed residual values into the
updated column for those rows where the sub-select did not find a match.
Basically, DB2 updated every row in TABLEA and when the sub-select failed
to find a matching row on TABLEB, it just re-used the B.COL1 value from the
last time it had found a matching row. I can sort of understand why DB2
updated every row, but I would have expected it to use Null or a default
value when the sub-select found no match.

Another person in my area wrote a similar query, but did not encounter the
same problem. The differences we noticed were that neither of the columns
being matched on in my sub-select were part of the tables' primary keys
whereas my co-workers sub-select matched on the primary keys of both tables
involved.

I re-wrote my query as shown below and got the desired results:

UPDATE TABLEA A
SET A.COL1 =
(SELECT B.COL1
FROM TABLEB B
WHERE A.COL2 = B.COL2)
WHERE EXISTS
(SELECT *
FROM TABLEB C
WHERE A.COL2 = C.COL2)






RICHARD E MOLERA

Re: questionable results with
(in response to Dave Nance)
John,

It would appear DB2 is functioning as requested by the UPDATE statement. In
other words, there is no predicate coded on the first UPDATE statement (i.e.,
which would explicitly limit the update of COL1 for only qualified rows).
Hence, mass update of COL1 for all rows.

Rick Molera






"John P. Schulting" <[login to unmask email]> on 01/10/2001 01:53:41 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: RICHARD E MOLERA/SallieMae)
Subject: questionable results with



I received the following question from one of our more experienced
developers and was wondering if anyone else had come across something
similar:

Note: We are at DB2 V6 on OS390.

I was trying make use of the recently(?) available capability to Set a
column equal to a value acquired via a subselect. It didn't work quite the
way I expected so I was wondering if there was any additional information
or safety tips that could be provided in its use.

Here is my query:

UPDATE TABLEA A
SET A.COL1 =
(SELECT B.COL1
FROM TABLEB B
WHERE A.COL2 = B.COL2)

Not all of the rows on TABLEA had matching rows on TABLEB and I thought
that the non-matching rows would not be updated. However, I checked the
data after running the query and DB2 had placed residual values into the
updated column for those rows where the sub-select did not find a match.
Basically, DB2 updated every row in TABLEA and when the sub-select failed
to find a matching row on TABLEB, it just re-used the B.COL1 value from the
last time it had found a matching row. I can sort of understand why DB2
updated every row, but I would have expected it to use Null or a default
value when the sub-select found no match.

Another person in my area wrote a similar query, but did not encounter the
same problem. The differences we noticed were that neither of the columns
being matched on in my sub-select were part of the tables' primary keys
whereas my co-workers sub-select matched on the primary keys of both tables
involved.

I re-wrote my query as shown below and got the desired results:

UPDATE TABLEA A
SET A.COL1 =
(SELECT B.COL1
FROM TABLEB B
WHERE A.COL2 = B.COL2)
WHERE EXISTS
(SELECT *
FROM TABLEB C
WHERE A.COL2 = C.COL2)








John P. Schulting

Re: questionable results with
(in response to RICHARD E MOLERA)
I guess I should be a little clearer with my question. The fact that all
rows on TableA are updated makes sense. What seems odd, however, is that
for those rows where there is not a matching row on TableB, the column in
TableA is not being set to null. The column is instead being set to the
value that was assigned to the column for the previous row. For example:
rows 1 and 2 have matching rows in TableB so their values are set properly.
Row 3 does not have a matching row in TableB, its value is set to the value
used for row 2.
John




(Embedded
image moved RICHARD E MOLERA <[login to unmask email]>
to file: @RYCI.COM>
pic05436.pcx) 01/10/2001 02:08 PM




Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: questionable results with


John,

It would appear DB2 is functioning as requested by the UPDATE statement.
In
other words, there is no predicate coded on the first UPDATE statement
(i.e.,
which would explicitly limit the update of COL1 for only qualified rows).
Hence, mass update of COL1 for all rows.

Rick Molera






"John P. Schulting" <[login to unmask email]> on 01/10/2001 01:53:41
PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: RICHARD E MOLERA/SallieMae)
Subject: questionable results with



I received the following question from one of our more experienced
developers and was wondering if anyone else had come across something
similar:

Note: We are at DB2 V6 on OS390.

I was trying make use of the recently(?) available capability to Set a
column equal to a value acquired via a subselect. It didn't work quite the
way I expected so I was wondering if there was any additional information
or safety tips that could be provided in its use.

Here is my query:

UPDATE TABLEA A
SET A.COL1 =
(SELECT B.COL1
FROM TABLEB B
WHERE A.COL2 = B.COL2)

Not all of the rows on TABLEA had matching rows on TABLEB and I thought
that the non-matching rows would not be updated. However, I checked the
data after running the query and DB2 had placed residual values into the
updated column for those rows where the sub-select did not find a match.
Basically, DB2 updated every row in TABLEA and when the sub-select failed
to find a matching row on TABLEB, it just re-used the B.COL1 value from the
last time it had found a matching row. I can sort of understand why DB2
updated every row, but I would have expected it to use Null or a default
value when the sub-select found no match.

Another person in my area wrote a similar query, but did not encounter the
same problem. The differences we noticed were that neither of the columns
being matched on in my sub-select were part of the tables' primary keys
whereas my co-workers sub-select matched on the primary keys of both tables
involved.

I re-wrote my query as shown below and got the desired results:

UPDATE TABLEA A
SET A.COL1 =
(SELECT B.COL1
FROM TABLEB B
WHERE A.COL2 = B.COL2)
WHERE EXISTS
(SELECT *
FROM TABLEB C
WHERE A.COL2 = C.COL2)



the










Sanjeev (CTS) S

Re: questionable results with
(in response to John P. Schulting)
John,
It really looks something different. Are we sure about what correlated
subquery returns(ofcourse true/false) in terms of value ?? But at the
same time why it should return NULL ??. DB2 returns only Sqlcode 100 with no
rows when it finds no rows with the matching criteria but it doen't return
NULL as a row. What will DB2 do in this case ??. I think it is confused, So
what should happen as a result to these types of queries ??.

Did you get some warning messages after running in Spufi. If it is still a
non-resovable issue then some experts/IBMers should reply otherwise i am not
even able to know why should it return NULL as well as the value you are
getting.

Let's see !!

Regards,
Sanjeev


> -----Original Message-----
> From: John P. Schulting [SMTP:[login to unmask email]
> Sent: Thursday, January 11, 2001 3:24 AM
> To: [login to unmask email]
> Subject: Re: questionable results with
>
> I guess I should be a little clearer with my question. The fact that all
> rows on TableA are updated makes sense. What seems odd, however, is that
> for those rows where there is not a matching row on TableB, the column in
> TableA is not being set to null. The column is instead being set to the
> value that was assigned to the column for the previous row. For example:
> rows 1 and 2 have matching rows in TableB so their values are set
> properly.
> Row 3 does not have a matching row in TableB, its value is set to the
> value
> used for row 2.
> John
>
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> To: [login to unmask email]
> cc: (bcc: RICHARD E MOLERA/SallieMae)
> Subject: questionable results with
>
>
>
> I received the following question from one of our more experienced
> developers and was wondering if anyone else had come across something
> similar:
>
> Note: We are at DB2 V6 on OS390.
>
> I was trying make use of the recently(?) available capability to Set a
> column equal to a value acquired via a subselect. It didn't work quite
> the
> way I expected so I was wondering if there was any additional information
> or safety tips that could be provided in its use.
>
> Here is my query:
>
> UPDATE TABLEA A
> SET A.COL1 =
> (SELECT B.COL1
> FROM TABLEB B
> WHERE A.COL2 = B.COL2)
>
> Not all of the rows on TABLEA had matching rows on TABLEB and I thought
> that the non-matching rows would not be updated. However, I checked the
> data after running the query and DB2 had placed residual values into the
> updated column for those rows where the sub-select did not find a match.
> Basically, DB2 updated every row in TABLEA and when the sub-select failed
> to find a matching row on TABLEB, it just re-used the B.COL1 value from
> the
> last time it had found a matching row. I can sort of understand why DB2
> updated every row, but I would have expected it to use Null or a default
> value when the sub-select found no match.
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



James Campbell

Re: questionable results with
(in response to Sanjeev (CTS) S)
A.COL1 should be set to null (when there isn't a correlated B.COL1) because
that's the behavoir described in the SQL Reference Manual for this
situation. Which is what I got in my quick test.

Sounds like 'bug' to me (I just don't know whose bug).

/* standard disclaimer */
James Campbell
DBA
Hansen Corporation, Doncaster
+61 3 9843 8442
[login to unmask email]
-----Original Message-----
From: S, Sanjeev (CTS) [mailto:[login to unmask email]
Sent: Thursday, January 11, 2001 7:56 PM
To: [login to unmask email]
Subject: Re: [DB2-L] questionable results with


John,
It really looks something different. Are we sure about what correlated
subquery returns(ofcourse true/false) in terms of value ?? But at the
same time why it should return NULL ??. DB2 returns only Sqlcode 100 with no
rows when it finds no rows with the matching criteria but it doen't return
NULL as a row. What will DB2 do in this case ??. I think it is confused, So
what should happen as a result to these types of queries ??.

Did you get some warning messages after running in Spufi. If it is still a
non-resovable issue then some experts/IBMers should reply otherwise i am not
even able to know why should it return NULL as well as the value you are
getting.

Let's see !!

Regards,
Sanjeev


> -----Original Message-----
> From: John P. Schulting [SMTP:[login to unmask email]
> Sent: Thursday, January 11, 2001 3:24 AM
> To: [login to unmask email]
> Subject: Re: questionable results with
>
> I guess I should be a little clearer with my question. The fact that all
> rows on TableA are updated makes sense. What seems odd, however, is that
> for those rows where there is not a matching row on TableB, the column in
> TableA is not being set to null. The column is instead being set to the
> value that was assigned to the column for the previous row. For example:
> rows 1 and 2 have matching rows in TableB so their values are set
> properly.
> Row 3 does not have a matching row in TableB, its value is set to the
> value
> used for row 2.
> John
>
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> To: [login to unmask email]
> cc: (bcc: RICHARD E MOLERA/SallieMae)
> Subject: questionable results with
>
>
>
> I received the following question from one of our more experienced
> developers and was wondering if anyone else had come across something
> similar:
>
> Note: We are at DB2 V6 on OS390.
>
> I was trying make use of the recently(?) available capability to Set a
> column equal to a value acquired via a subselect. It didn't work quite
> the
> way I expected so I was wondering if there was any additional information
> or safety tips that could be provided in its use.
>
> Here is my query:
>
> UPDATE TABLEA A
> SET A.COL1 =
> (SELECT B.COL1
> FROM TABLEB B
> WHERE A.COL2 = B.COL2)
>
> Not all of the rows on TABLEA had matching rows on TABLEB and I thought
> that the non-matching rows would not be updated. However, I checked the
> data after running the query and DB2 had placed residual values into the
> updated column for those rows where the sub-select did not find a match.
> Basically, DB2 updated every row in TABLEA and when the sub-select failed
> to find a matching row on TABLEB, it just re-used the B.COL1 value from
> the
> last time it had found a matching row. I can sort of understand why DB2
> updated every row, but I would have expected it to use Null or a default
> value when the sub-select found no match.
>
----------------------------------------------------------------------------
-------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged
information.
If you are not the intended recipient, please contact the sender by reply
e-mail and
destroy all copies of the original message. Any unauthorised review, use,
disclosure,
dissemination, forwarding, printing or copying of this email or any action
taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------
------------------------------------------------------------
----------------------------------------------------------------------------
------------------------------------------------------------







**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************



[login to unmask email]

Re: questionable results with
(in response to James Campbell)
John,

I would raise this with IBM, it may be working as designed but I would have
thought that returning a NULL would be more consistent.

Jim.

-----Original Message-----
From: John P. Schulting [mailto:[login to unmask email]
Sent: 10 January 2001 18:54
To: [login to unmask email]
Subject: questionable results with


I received the following question from one of our more experienced
developers and was wondering if anyone else had come across something
similar:

Note: We are at DB2 V6 on OS390.

I was trying make use of the recently(?) available capability to Set a
column equal to a value acquired via a subselect. It didn't work quite the
way I expected so I was wondering if there was any additional information
or safety tips that could be provided in its use.

Here is my query:

UPDATE TABLEA A
SET A.COL1 =
(SELECT B.COL1
FROM TABLEB B
WHERE A.COL2 = B.COL2)

Not all of the rows on TABLEA had matching rows on TABLEB and I thought
that the non-matching rows would not be updated. However, I checked the
data after running the query and DB2 had placed residual values into the
updated column for those rows where the sub-select did not find a match.
Basically, DB2 updated every row in TABLEA and when the sub-select failed
to find a matching row on TABLEB, it just re-used the B.COL1 value from the
last time it had found a matching row. I can sort of understand why DB2
updated every row, but I would have expected it to use Null or a default
value when the sub-select found no match.

Another person in my area wrote a similar query, but did not encounter the
same problem. The differences we noticed were that neither of the columns
being matched on in my sub-select were part of the tables' primary keys
whereas my co-workers sub-select matched on the primary keys of both tables
involved.

I re-wrote my query as shown below and got the desired results:

UPDATE TABLEA A
SET A.COL1 =
(SELECT B.COL1
FROM TABLEB B
WHERE A.COL2 = B.COL2)
WHERE EXISTS
(SELECT *
FROM TABLEB C
WHERE A.COL2 = C.COL2)







***************************************************************
The contents of this Email and any files transmitted with it
are confidential and intended solely for the use of the
individual or entity to whom it is addressed. The views stated
herein do not necessarily represent the view of the company.
If you are not the intended recipient of this Email you may not
copy, forward, disclose or otherwise use it or any part of it
in any form whatsoever. If you have received this mail in
error please Email the sender.
***************************************************************



Edward Vassie

Re: questionable results with
(in response to Jim.Leask@RS-COMPONENTS.COM)
I also think this should be raised with IBM. I can see no case for saying
anything other than a NULL should be returned. If IBM say it is WAD, then
the design is wrong.

If your query:

UPDATE TABLEA A
SET A.COL1 =
(SELECT B.COL1
FROM TABLEB B
WHERE A.COL2 = B.COL2)

cannot find a match between TABLEA and TABLEB, then there can be no
justification for DB2 to update COL1 with a value returned for a previous
match. If this was justified, it would mean that the results of the update
are undefined, because the order of processing rows from TABLEA cannot be
predicted. (The order cannot be predicted because more than 1 read engine
may be scheduled on TABLEA by DB2, and the rows will be processed as they
get returned by each read engine.)

It goes against all of the little I know about SQL and set theory that the
results of an UPDATE statement could correctly be said to be undefined.

From Edward Vassie...


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: 11 January 2001 09:32
To: [login to unmask email]
Subject: Re: questionable results with


John,

I would raise this with IBM, it may be working as designed but I would have
thought that returning a NULL would be more consistent.

Jim.

-----Original Message-----
From: John P. Schulting [mailto:[login to unmask email]
Sent: 10 January 2001 18:54
To: [login to unmask email]
Subject: questionable results with


I received the following question from one of our more experienced
developers and was wondering if anyone else had come across something
similar:

Note: We are at DB2 V6 on OS390.

I was trying make use of the recently(?) available capability to Set a
column equal to a value acquired via a subselect. It didn't work quite the
way I expected so I was wondering if there was any additional information
or safety tips that could be provided in its use.

Here is my query:

UPDATE TABLEA A
SET A.COL1 =
(SELECT B.COL1
FROM TABLEB B
WHERE A.COL2 = B.COL2)

Not all of the rows on TABLEA had matching rows on TABLEB and I thought
that the non-matching rows would not be updated. However, I checked the
data after running the query and DB2 had placed residual values into the
updated column for those rows where the sub-select did not find a match.
Basically, DB2 updated every row in TABLEA and when the sub-select failed
to find a matching row on TABLEB, it just re-used the B.COL1 value from the
last time it had found a matching row. I can sort of understand why DB2
updated every row, but I would have expected it to use Null or a default
value when the sub-select found no match.

Another person in my area wrote a similar query, but did not encounter the
same problem. The differences we noticed were that neither of the columns
being matched on in my sub-select were part of the tables' primary keys
whereas my co-workers sub-select matched on the primary keys of both tables
involved.

I re-wrote my query as shown below and got the desired results:

UPDATE TABLEA A
SET A.COL1 =
(SELECT B.COL1
FROM TABLEB B
WHERE A.COL2 = B.COL2)
WHERE EXISTS
(SELECT *
FROM TABLEB C
WHERE A.COL2 = C.COL2)







***************************************************************
The contents of this Email and any files transmitted with it
are confidential and intended solely for the use of the
individual or entity to whom it is addressed. The views stated
herein do not necessarily represent the view of the company.
If you are not the intended recipient of this Email you may not
copy, forward, disclose or otherwise use it or any part of it
in any form whatsoever. If you have received this mail in
error please Email the sender.
***************************************************************