Re: Can some explain this to me?
- From: "Roy Harvey (SQL Server MVP)" <roy_harvey@xxxxxxxx>
- Date: Thu, 25 Oct 2007 15:49:06 GMT
The behavior is expected, if very painful in this case.
A subquery can reference any column in any table in the FROM clause of
the subquery, but it can also reference any column in any table from
the outer query. The reference to pers_companyid in the subquery was
resolved against the outer query table person. Had it been a column
in the subquery table company it would have acted as you intended.
The way to avoid this is to always qualify all column references in a
subquery. For a query such as this it would also make sense to
execute the subquery alone before executing the DELETE. It is also a
good idea to run a query first that establishes how many rows are to
be deleted and check that the number makes sense. Then run the DELETE
inside a transaction and if the count is different roll the
transaction back.
Roy Harvey
Beacon Falls, CT
On Thu, 25 Oct 2007 11:27:23 -0000, davidaustinarcher@xxxxxxxxx wrote:
Hello,.
I run the following query on a database (SQL 2005):
delete from person where pers_companyid in (select pers_companyid from
company where comp_expiry is not null)
I wanted to delete all people associated with a company which had an
expiry date. The mistake I made was that the column pers_companyid
does not exist. It should have been comp_companyid.
However, the query ran anyway and deleted all records from my person
table? If I run the subquery on its own then it doesn't run as the
column is missing.
Shouldn't I have got an error running this query?
Thanks,
David
- References:
- Can some explain this to me?
- From: davidaustinarcher
- Can some explain this to me?
- Prev by Date: MCDBA - SQL Server 2005 Certification?
- Next by Date: Re: Weird speed problem
- Previous by thread: Can some explain this to me?
- Next by thread: Re: Can some explain this to me?
- Index(es):
Relevant Pages
|
