Re: Unusual/questionable SQL behaviour
Date: 1996/07/21
Message-ID: <31F2608E.1FD5_at_ynp.dialup.access.net>#1/1
Paul Young wrote:
>
> Take the foillowing query:
> UPDATE BIG_TABLE
> SET MAIN_VARIABLE = 1
> WHERE KEY IN (SELECT KEY
> FROM SMALLER_TABLE);
>
> TABLE: BIG_TABLE
> KEY <primary key>
> MAIN_VARIABLE
> ...
>
> TABLE: SMALLER_TABLE
> ALTERNATE_KEY <primary key>
> ...
>
> KEY is the primary key for BIG_TABLE. However in SMALLER_TABLE,
> the column equivelent to KEY is named ALTERNATE_KEY. The feild
> KEY does not exist in SMALLER_TABLE. Suppose there are 10
> records in BIG_TABLE. Suppose there are 2 records in SMALLER_TABLE
> but they exist in BIG_TABLE.
>
> What do you think this query does? It might surprise you.
>
> OK, I'll cut the suspense. It updates every row of BIG_TABLE.
> Think about it. This is probably correct behavior but it is
> probably not waht you'd expect.
Nothing unusual/questionable here: the nested query's SELECT clause is treated as 'SELECT <constant>' where <constant> == KEY, so the query returns two (IAW table sizes in your example) identical rows which naturally satisfy IN predicate for every given KEY from BIG_TABLE.
+----------------------------------------------------------------------+ | Youri N. Podchosov (ynp) \\\ E-mail: ynp_at_ynp.dialup.access.net || UNIX Systems Administrator ))) WWW: http://ynp.dialup.access.net | | The Davidsohn Group, NYC /// B:(212)208-0129, H:(718)234-4140 |
+----------------------------------------------------------------------+Received on Sun Jul 21 1996 - 00:00:00 CEST