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

JWColby jwcolby at colbyconsulting.com
Mon Jan 22 21:00:16 CST 2007


I have a set of tables.  tblSurvey contains 65 million records, about 600
fields, from "big table" which was 65 million records but which contained
about 100 now useless fields.  I.e. I made a smaller table by copying 600 of
700 fields.  I included the original integer autoincrement PK from the big
table into tblSurvey.
 
I have a second table that took people / address info from "big table" and
processed it to eliminate bad addresses, turning it into tblAZ.  tblAZ now
contains about 53 million records, including the PK from Big Table.  
 
So tblAZ contains 53 million records ALL OF WHICH contain known valid
addresses, and which is a subset of the records in Big Table.
 
So...
 
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.
 
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?  
 
Again I need to eliminate all survey records where the PK is not in a table
that has been address validated (tblAZ) and which contains fewer records
than tblSurvey.
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 



More information about the dba-SQLServer mailing list