Unusual/questionable SQL behaviour

From: Paul Young <youngpa_at_statcan.ca>
Date: 1996/07/19
Message-ID: <Dusvoq.K3D_at_statcan.ca>#1/1


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 #
################################################################
Received on Fri Jul 19 1996 - 00:00:00 CEST

Original text of this message