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

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 (AT) gmail (DOT) com wrote:

Google for a recent posting of mine on scoping rules in SQL. Then
learn how to properly name data elements -- why is a personnel
company_id a totally different kind of attribute from a company
company_id? That is what different names imply! If you had obeyed
ISO-11179, your query would have run as intended.

DELETE FROM Personnel -- collective name
WHERE company_id -- scopes to Personnel
IN (SELECT company_id -- scopes to Companies
FROM Companies -- plural name
WHERE contract_expiry IS NOT NULL);

Do entire companies expire in your data model? or does some
relationship between them and your personnel expire?

Get a copy of SQL PROGRAMMING STYLE and it will help clear up your
thinking.