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 SouzaBecause 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>...
> 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.|||> 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
--|||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.

Invalid Column Name does not cause error

Hi,
I have a SQL Server 2000 sproc that selects data into a temporary table.
If certain conditions are met, the sproc then uses a join on the temporary
table to update an other table.
In the join for this update, the join column on the temporary table is named
incorrectly.
This code is called as part of a sequence of calls to hundreds of sprocs.
The thing is the code has never raised an error, the successful execution of
the sproc is logged to another table. I would expect the code to raise
something like
Server: Msg 207, Level 16, State 3, Line ..
Invalid column name 'dddddddd'.
I am aware of deferred name resolution but I think that when SQL Server
compiles the procedure the error should be raised.
Of course I can edit the sproc but the client requires evidence of
malfunction. Any help appreciated.Could you please post the DDL of the offending procedure?
ML|||The table is created with a column rt_pol_num. The join is the non-existent
column num_pol_dcrt. I have called the sproc with the recompile option but
the error is not raised.
BEGIN
SELECT DISTINCT
rt_pol_num = DCAM.num_pol_dcrt
Blah, Blah …
INTO #tdcam_t
FROM dcam_t dcam
WHERE dcam.stge_rec_err_ind <> 'Y' AND
dcam.prces_act_cd IN('ISRT') AND
EXISTS
(
SELECT src_key_vlu_txt
FROM alternate_payee_t
WHERE alternate_payee_t.src_key_vlu_txt = 'DCAM' +
DCAM.num_pol_dcrt +
DCAM.num_cert_dcrt +
DCAM.ctlnum_reg_cntrl_number +
UPPER(REPLACE(CONVERT(CHAR(11), DCAM.dte_alt_mail_eff_dcam, 106),
' ', ''))
)
SELECT @.vErrNum = @.@.ERROR, @.vIsrtRecCount = @.@.ROWCOUNT, @.vStatrcTblNm =
'dcam_t'
IF @.vErrNum <> 0
BEGIN
SELECT @.vErrLocNm = 'cannot select from dcam_t into #tdcam_t',
@.vErrTblNm = 'dcam_t',
@.vStatDsc = 'procedure failed'
GOTO ON_ERROR
END
ELSE
SELECT @.vDropTmp = 'DROP TABLE #tdcam_t'
IF @.vIsrtRecCount > 0
BEGIN
IF @.pWriteTable <> 'N'
BEGIN
UPDATE dcam_t
SET stge_rec_err_ind = 'Y'
WHERE EXISTS
(
SELECT stge_rec_err_ind
FROM #tdcam_t TT
WHERE TT.num_pol_dcrt = dcam_t.num_pol_dcrt
)
SELECT @.vErrNum = @.@.ERROR, @.vUpdtRecCount = @.@.ROWCOUNT, @.vStatrcTblNm =
'dcam_t'
IF @.vErrNum <> 0
BEGIN
SELECT @.vErrLocNm = 'cannot update dcam_t',
@.vErrTblNm = 'dcam_t',
@.vStatDsc = 'procedure failed'
GOTO ON_ERROR
END|||Non-existing Tables are not schema validated. If the parser notifies a
table which exists the columns are checked for existence, but not if
the table isn=B4t present during compilation time (like in your case)
CREATE PROCEDURE Testproc
AS
BEGIN
CREATE TABLE #testtable
(
SomeColumn int
)
Select somenotexsitingcolumn from #testtable
END
--VS
CREATE TABLE testtable
(
SomeColumn int
)
CREATE PROCEDURE Testproc2
AS
BEGIN
Select somenotexsitingcolumn from testtable
END
HTH, Jens Suessmeyer.|||The OP also states there are no errors at execution.
ML|||Hi,
The error is not raised because during compilation the temporary table does
not exist and SQL Server defers examination of the statement until run-time.
@.vIsrtRecCount > 0
is always false and so the code is never entered. If
@.vIsrtRecCount > 0
is true, the error 'Invalid column name' occurs.
Thanks to all who replied.

Introducing FOREIGN KEY constraint

"Introducing FOREIGN KEY constraint 'FK__APPLICANTMA__SEX__414EAC47' on table 'APPLICANTMASTER' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

ive a foreign key reference on the table APPLICANT MASTER
of the form
FOREIGN KEY (SEXCODE) REFERENCES
APPLICANTSEX(SEXCODE)
ON DELETE NO ACTION
ON UPDATE CASCADE,

can any one help me WHAT THE MESSAGE MEANS ?Originally posted by baburajv
"Introducing FOREIGN KEY constraint 'FK__APPLICANTMA__SEX__414EAC47' on table 'APPLICANTMASTER' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

ive a foreign key reference on the table APPLICANT MASTER
of the form
FOREIGN KEY (SEXCODE) REFERENCES
APPLICANTSEX(SEXCODE)
ON DELETE NO ACTION
ON UPDATE CASCADE,

can any one help me WHAT THE MESSAGE MEANS ?

hi,
this link below explains it all:
http://lists.evolt.org/archive/Week-of-Mon-20030421/139403.html
harshal.|||Hi,

that was useful information, but i have doubts,

my table is

ApplicantSex
(
SexCode tinyint,
Sex varchar(6),
constraint pkApplicantSex primary key(SexCode)
)

Applicant
(
AppId bigint,
Name varchar(30),
SexCode tinyint,
constraint pkApplicant PRIMARY KEY (AppId),
constraint fkApplicant FOREIGN KEY (SexCode)
references ApplicantSex(SexCode)
on delete no action
on update cascade
)


here, i can see no "cycles" or "multiple "cascade paths"

then why sql server says

"Introducing FOREIGN KEY constraint 'FK__APPLICANTMA__SEX__414EAC47' on table 'APPLICANTMASTER' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

pl give some info|||Originally posted by baburajv
Hi,

that was useful information, but i have doubts,

my table is

ApplicantSex
(
SexCode tinyint,
Sex varchar(6),
constraint pkApplicantSex primary key(SexCode)
)

Applicant
(
AppId bigint,
Name varchar(30),
SexCode tinyint,
constraint pkApplicant PRIMARY KEY (AppId),
constraint fkApplicant FOREIGN KEY (SexCode)
references ApplicantSex(SexCode)
on delete no action
on update cascade
)


here, i can see no "cycles" or "multiple "cascade paths"

then why sql server says

"Introducing FOREIGN KEY constraint 'FK__APPLICANTMA__SEX__414EAC47' on table 'APPLICANTMASTER' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

pl give some info
when do u get this message while delete or while update??
while updation there is a cascade from applicant master to applicant to applicant sex.|||I GET THIS MESSAGE WHILE RUNNING MY SCRIPT FILE (.SQL FILE) FROM THE QUERY ANALYZER.

ONE MORE THING,

"while updation there is a cascade from applicant master to applicant to applicant sex."

i need to know one thing, the "applicantmaster" referes to sexcode in "applicantsex" and

" ON UPDATE CASCADE" means any updation in applicant sex must be cascaded
and not the reverse..(am i correct?)

i hope i made my point clear

thanks for the advice