Select where not exists

[Michael]

I have a table that has the following fields:

tblECHECK
ID (autonumber identity column)
PTID
Batchnum
Page
DataPoint
DPValue

There are many other tables on which I run a script that tests to make
sure the data meets certain entry requirements (I don't want to put
restrictions on the forms because the data entry personel have to
enter what is on the survey).

Anything that fails is copied to the tblECHECK table. All the data
copied into the table are identified by a batchnumer (batchnum).

I want to write a script that will look at all the records in the
tblECHECK table, and delete any duplicates (the batch numbers are the
only fields that would be different).

I have tried the following with no success (it returns 0 records):

Select *
From tblECHECK
Where batchnum ='batch8' and not exists (select * from tblECHECK where
batchnum = 'batch7')

Any help would be appreciated.

[--CELKO--]

>> I have a table that has the following fields [sic]: <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

Columns are not fields; rows are not records; IDENTITY is not a key by
definition. Stop putting that silly, redundant prefix "tbl-" in front
of tabel names -- this is SQL and Fortran II or BASIC. Is this what
you really wanted to say, ignoring DEFAULTs, CHECK() and DRI
constraints ?

CREATE TABLE Surveys
(batchnum INTEGER NOT NULL,
ptid INTEGER NOT NULL,
page INTEGER NOT NULL,
datapoint INTEGER NOT NULL,
dpvalue INTEGER NOT NULL,
PRIMARY KEY (batchnum, ptid, page, datapoint))

I am guessing that batchnum is the physical number of a batch from
data entry (I grew up with punch cards, so I am familiar with this
approach; we don't use it with relational databases and on-line
terminals very much any more), ptid is the survey identifier, page is
a physical page number in the survey form, datapoint is a question on
a page and dpvalue is the answer to that question.

I want to write a script that will look at all the records [sic]in

the ECHECK table, and delete any duplicates (the batch numbers are the
only fields [sic] that would be different). <<

The trick is to exlcude them in the first place by having procedures
in place and proper constraints on the table. Constraints and DRI are
some of the many differences between a file system (fields and
records) and a relational database. But it is time for a kludge:

DELETE FROM Surveys
WHERE batchnum
IN (SELECT batchnum
FROM Surveys AS S1
WHERE Surveys.ptid = S1.ptid
AND Surveys.page = S1.page
AND Surveys.datapoint = S1.datapoint
AND Surveys.datapoint = S1.datapoint
AND Surveys.batchnum < S1.batchnum);



I have tried the following with no success (it returns 0 records):

Select *
From tblECHECK
Where batchnum ='batch8' and not exists (select * from tblECHECK where
batchnum = 'batch7')

Any help would be appreciated.