[dba-SQLServer] Delete from a "not in" view

On 22 Jan 2007 at 22:00, JWColby wrote:

> I want to eliminate any records in tblSurvey where the PKID is not found in
> tblAZ.  The PKs from tblSurvey and tblAZ both came from Big Table.  Thus it
> is a classic "not in", where I want to eliminate records in tblSurvey where
> PKID is inner joined to tblAZ, and where tblAZ PKID is null.
>  

If it's an Inner Join, there will be no records in tblAZ where PKID is null!

> Unfortunately, SQL Server says that such a "not in" query is not updateable,
> which means I can't delete records in tblSurvey, nor can I create a blank
> field which I update to True if the PK is not in tblAZ.
>  
> Why is this query not updateable, and how do I accomplish my objective?  

What is your SQL?

This will work:

delete 
from tblSurvey
from
tblSurvey left join tblAZ
on tblSurvey.pkid = tblAZ.pkid
where tblAZ.pkid is null