Unusual/questionable SQL behaviour
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)
################################################################Received on Fri Jul 19 1996 - 00:00:00 CEST
# Paul Young #
# youngpa_at_statcan.ca #
# Statistics Canada #
################################################################