Invalid Udate SQL statement DOES NOT cause error... Does anyone know why??

Here's my update statement:

UPDATE Item1
SET reviewloop = 1, currentreviewstate=5
WHERE itemid in
(SELECT itemid FROM Item2)

The thing is: the table Item2 DOES NOT HAVE a field called itemid.
So, I should receive an error, right? Not so.Instead, every single
record in Item1 was updated.

Does anyone know why SQL Serverr does not trown an error???

Thanks guys,

-Silvio Souza

Because the sub query can reference fields from the update. itemid in this
case will be retrieved from Item1.

The rule for subqueries is that a column name that can't be resolved to
column within the subquery is assumed to reference a column in the outer
query. If in doubt, use the two-part column name including the table
name/alias.

--
David Portas
SQL Server MVP
--

"no spam" <chuck@sheckmedia.com> wrote in message news:<vCWhc.71068$Lh2.5553@bignews1.bellsouth.net> ...[color=blue]
> Because the sub query can reference fields from the update. itemid in this
> case will be retrieved from Item1.[/color]

I don't think so. SQL certainly doesn't say to itself "Since I can't
find that value in Item2 I'll assume that they must mean the value in
Item1" - that would be catastrophic.

I've just tried this myself, and whilst it didn't give any error, it
didn't update any rows in Item1 either. This makes sense, because
the subquery is simply evaluating to FALSE, so 0 rows are updated in
the main query.

> I don't think so. SQL certainly doesn't say to itself "Since I can't[color=blue]
> find that value in Item2 I'll assume that they must mean the value in
> Item1" - that would be catastrophic.[/color]

The problem isn't to do with *values* it's to do with resolution of *column
names*. Substitute the word "column" for "value" and your statement
describes exactly what SQL does.

Assuming the column Itemid doesn't exist in Item2, the UPDATE statement you
posted is equivalent to:

UPDATE Item1
SET reviewloop = 1, currentreviewstate=5
WHERE Item1.itemid IN
(SELECT Item1.itemid FROM Item2)

As long as there is at least one row in Item2, every row in Item1 should get
updated.

--
David Portas
SQL Server MVP
--

Any field reference in a sub query will always look for the field internally
first and if not found it will look in the outer query. The reason for this
behaviour is that the sub query can use values from the outer query as
selection criteria, in case statements etc.

This is not a bug, it is by design. By always using table qualifiers in all
sql it will never cause a problem even if the developer mistypes a field
name.
Sloppy SQL (without proper table qualifiers etc) may behave funny as in the
example provided by the OP.

Also, if you look at the execution plan for this and similar queries it will
be more clear why. The optimizer usually turn sub queries like this into
joins.