|
|
Posted by Roy Harvey (SQL Server MVP) on 10/25/07 15:49
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@gmail.com 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
Navigation:
[Reply to this message]
|