Prev: Writing to exception logs with in a transaction
Next: Subquery help
From: Joachim Hofmann on 12 Jan 2010 16:38
Hello,

I once had an UPDATE - Statement through IN() like this:

>>>
/* Update A from join of A, B, C which gives A_Key */
UPDATE A
SET ...
WHERE A.A_Key IN
(
SELECT A.A_Key
FROM
A INNER JOIN B ON ...
INNER JOIN C ON ...
)
<<<

/* Update A from join of A, B, C which gives A_Key */
Then I catched the output of A_Keys of the inner SELECT and "feeded" the UPDATE - Statement
directly with the A_Keys:

>>>
UPDATE A
SET ...
WHERE A.A_Key IN
(
47,
11,
08,
15,
...
)
<<<

To my astonishment the first statement gave *many more "rows affected"* than the second one.

Why could these two statements differ in behaviour?
Are there any special considerations or pitfalls in such an IN() - statement?

Thank You

Joachim
From: Tom Cooper on 12 Jan 2010 22:55
AFAIK, there should be no difference in the number of rows updated from
those two statements if you have the same values in the list as is returned
by the select statement. I would be very interested if you could provide an
example that reproduces this problem.

And there are no particular considerations in using IN in this manner.
However, you do have to be careful if you are using NOT IN and the list of
values (either returned by a SELECT subquery or if you provide a list of
literal values) contains at least one NULL. If that happens, NO rows will
be updated.

Tom

"Joachim Hofmann" <speicher(a)freene.de> wrote in message
news:enDib38kKHA.2780(a)TK2MSFTNGP05.phx.gbl...
> Hello,
>
> I once had an UPDATE - Statement through IN() like this:
>
> >>>
> /* Update A from join of A, B, C which gives A_Key */
> UPDATE A
> SET ...
> WHERE A.A_Key IN
> (
> SELECT A.A_Key
> FROM
> A INNER JOIN B ON ...
> INNER JOIN C ON ...
> )
> <<<
>
> /* Update A from join of A, B, C which gives A_Key */
> Then I catched the output of A_Keys of the inner SELECT and "feeded" the
> UPDATE - Statement directly with the A_Keys:
>
> >>>
> UPDATE A
> SET ...
> WHERE A.A_Key IN
> (
> 47,
> 11,
> 08,
> 15,
> ...
> )
> <<<
>
> To my astonishment the first statement gave *many more "rows affected"*
> than the second one.
>
> Why could these two statements differ in behaviour?
> Are there any special considerations or pitfalls in such an IN() -
> statement?
>
> Thank You
>
> Joachim

 | 
Pages: 1
Prev: Writing to exception logs with in a transaction
Next: Subquery help