CODEWITHSUNDEEP
sqlsql-server
Kirsty T
Kirsty T

Reputation: 23

How does SQL handle (or not) an error within a where clause?

I accidentally deleted all the rows in the MULTIPLECALCCLASSDEF table with the following:

delete
from MULTIPLECALCCLASSDEF
where SCHEMEHISTID in (select SCHEMEHISTID from SCHEME where SCHEMEID = @intScheme)

which initially looked fine. The problem looks to be that there's no SCHEMEHISTID in the SCHEME table, but as this is the case why doesn't the where clause return an error, or no rows?

This errors (Invalid column name SCHEMEHISTID):

select SCHEMEHISTID from SCHEME where SCHEMEID = @intScheme

But this doesn't:

select *
from MULTIPLECALCCLASSDEF
where SCHEMEHISTID in (select SCHEMEHISTID from SCHEME where SCHEMEID = @intScheme)

Upvotes: 2

Views: 81

Answers (3)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

As Rahul mentioned, SCHEMEHISTID column is included into MULTIPLECALCCLASSDEF table. You can check easily by executing following queries:

select *
from MULTIPLECALCCLASSDEF AS h
where h.SCHEMEHISTID in (select h.SCHEMEHISTID from SCHEME AS s where s.SCHEMEID = @intScheme)

vs.

select *
from MULTIPLECALCCLASSDEF AS h
where h.SCHEMEHISTID in (select s.SCHEMEHISTID from SCHEME AS s where s.SCHEMEID = @intScheme)

The last one should raise an error.

Another solution is to use system catalog views:

SELECT s.name, t.name, c.name
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.columns c ON c.object_id = t.object_id
WHERE s.name = N'dbo'
AND t.name IN (N'MULTIPLECALCCLASSDEF', N'SCHEME')
AND c.name = N'SCHEMEHISTID'

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172518

Its because the column which you have added in your outer query is available to your subquery ie., in your table MULTIPLECALCCLASSDEF. If you would name it to some other name for eg MULTIPLETEST and try then it would result in error. This is the reason why it is advised to use alias name along with the column name when you are using such queries.

So it should be like

delete 
    from MULTIPLECALCCLASSDEF 
    where SCHEMEHISTID in (select MULTIPLECALCCLASSDEF.SCHEMEHISTID from SCHEME  where SCHEMEID = @intScheme);

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270401

In general, you should use table aliases and qualified column names (including the table alias). This is especially true when you are using correlated subqueries. And you just found out why.

The query you intended was:

delete c
    from MULTIPLECALCCLASSDEF c
    where c.SCHEMEHISTID in (select s.SCHEMEHISTID from SCHEME s where s.SCHEMEID = @intScheme);

This would have generated an error.

Without the aliases, the query is interpreted as:

delete c
    from MULTIPLECALCCLASSDEF c
    where c.SCHEMEHISTID in (select c.SCHEMEHISTID from SCHEME s where s.SCHEMEID = @intScheme);

Well, assuming there is any match to the where, then the in is trivially satisfied by any non-NULL value for SCHEMEHISTID.

Simple solution: Always use qualified column names.

Upvotes: 3

Related Questions