By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 974 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Because the sub query can reference fields from the update. itemid in this
case will be retrieved from Item1.
Jul 20 '05 #2

P: n/a
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
--
Jul 20 '05 #3

P: n/a
"no spam" <ch***@sheckmedia.com> wrote in message news:<vC******************@bignews1.bellsouth.net> ...
Because the sub query can reference fields from the update. itemid in this
case will be retrieved from Item1.


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.
Jul 20 '05 #4

P: n/a
> 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.


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
--
Jul 20 '05 #5

P: n/a
KA
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.
Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.