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