Re: Unusual/questionable SQL behaviour

In article <Dusvoq.K3D_at_statcan.ca>, youngpa_at_statcan.ca says...
>
>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.
>
>Watch out.
>
>
>PGY (Opinions expressed are mine and mine alone)
>################################################################
># Paul Young #
># youngpa_at_statcan.ca #
># Statistics Canada #
>################################################################

I don't know if this is unusual or questionable, but it looks highly data dependent to me.

-- 
Tony Noble
tnoble_at_mt.gov
Opinions expressed do not necessarily reflect those of my employer.