473,836 Members | 1,509 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Select where not exists

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.
Jul 20 '05 #1
1 11723
>> 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.datapoi nt = S1.datapoint
AND Surveys.datapoi nt = S1.datapoint
AND Surveys.batchnu m < 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.

Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
42199
by: Samuel Hon | last post by:
Hi I'm not sure what the best approach for this is: I have a stored procedure which I would like to use to return several output values instead of returning a recordset. CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 int OUTPUT) AS SELECT field2, field3 FROM Table WHERE field1 = @param1
3
3553
by: Ian T | last post by:
Hi, I've got what I think (probably incorrectly) should be a simple SELECT : Two colums with data like col1 col2 1 50 1 51 2 50
8
5261
by: Trev | last post by:
Which is more efficient: Select * from table1 where id in (select id from table2) or Select * from table1 where exists(select * from table2 where table2.id=table1.id)
2
22110
by: Bane | last post by:
Hi all In the SP below im (trying to) do some dynamic sql. As you can see the table to use is set as a variable and the 'exec' method used to run the sqlstatements. My problem is that the 'if exists' method is not doing what i was hoping it could do. The @presql command returns somewhere between 0 or 50 rows (give and take) - i just want the 'if exists' part to determine if the select statement returns something or not since i then...
4
2870
by: jimh | last post by:
I'm not a SQL expert. I want to be able to write a stored procedure that will return 'people who bought this product also bought this...'. I have a user table that links to a transaction table that links to a transaction items table that links to the products table: (User Table) UserID Other user data
10
5643
by: serge | last post by:
Using "SELECT * " is a bad practice even when using a VIEW instead of a table? I have some stored procedures that are identical with the difference of one statement in the WHERE clause. If I create a single View and specify also in this View the WHERE clause that is common in these stored procedures, I will have the new stored procecures changed to be like:
4
5490
by: Chris Kratz | last post by:
Hello all, We have run into what appears to be a problem with rules and subselects in postgres 7.4.1. We have boiled it down to the following test case. If anyone has any thoughts as to why this would be happening, we would appreciate feedback. We have tested on 7.3.4, 7.3.6 and 7.4.1 and all exhibit the same behavior. Test case one tries to populate table2 from table1 with records that are not in table2 already. Table2 gets...
1
5844
by: bpforte | last post by:
Hello, I need help with building query, basically I need to select all records from one table that don't exists in second table with status 1, but they can exists in second table with status 0, to be more complicated there can be in the same record in second table with status 0 and 1 (second table is something like log) in oracle I can do: select record from table_1 where record is not in (select record from table_2 where status=1)
4
5612
by: qwedster | last post by:
Howdy folks! I am using stored procedure to see if a value exists in database table and return 0 if exists or else -1, in the following SQL queries. However how to check if a value (that is null) exists in database table and return 0 if exists or else -1 using stored procedure? Please help. USE GO IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabase')
2
14670
by: qwedster | last post by:
Folk! How to programattically check if null value exists in database table (using stored procedure)? I know it's possble in the Query Analyzer (see last SQL query batch statements)? But how can I pass null value as parameter to the database stored procedure programattically using C#? Although I can check for empty column (the following code passes string.Empty as parameter but how to pass null value?), I cannot check for null value...
0
9820
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9670
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10844
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10548
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7792
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6979
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5826
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4016
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.